Query/db Optimization

[TheGallery]

Hello everyone Yet another problem occurred. I am making a website, and visitors have to choose their country. Now, there is an option to select a country and see which people have visited the site from the said country. My query looks like this "SELECT visitor_name FROM visitors_database WHERE country=country_name" and i have a loop in the php script that starts from the first visitor entry(1) to the last entry(n) (that's total visitors in the website). This means that it has to go through all the visitors, (and this number can be pretty high), to find the right results. So it takes time to load the page and show the results. The solution i thought of, is to have a table for each country and store the visitors there. My question is this: is this the best way i can follow for faster page load? If no what is it? I am pretty much new in php and databases and i am still learning this kind of stuff

[JamesB]

and i have a loop in the php script that starts from the first visitor entry(1) to the last entry(n) (that's total visitors in the website). This means that it has to go through all the visitors, (and this number can be pretty high), to find the right results.

just to confirm, your query isn't getting sent on each loop iteration right? as the query only needs to be sent once with mysql_query. i've got a feeling having a table for each country might be fastest, but i wouldn't do or recommend that. personally i would have a table for a list of countries, with fields (int) country_id and (varchar) country_name,that way the visitors table would only have to store an integer (the country_id) for each visitor row, which would probably speed up the WHERE in the query, as well as storing less data. you could also do something like this:visitors table - (int) visitor_id, (varchar) visitor_name, etccountries table - (int) country_id, (varchar) country_name - or could be in a php array if country names aren't dynamically retrievedvisitor_countries table - (int) visitor_id, (int) country_id which i think would make the query very fast on the visitor_countries table, using something like: (untested)

SELECT visitors.visitor_name FROM visitor_countries WHERE visitor_countries.country_id = 5 INNER JOIN visitors ON visitor_countries.visitor_id = visitors.visitor_id

[TheGallery]

just to confirm, your query isn't getting sent on each loop iteration right? as the query only needs to be sent once with mysql_query.

I think here is the mistake. My loop looks like this:

// $i represents the visitor IDfor ($i = 1; $i < $total_visitors: $i++)	echo get_visitor_names($country_name, $i);

The function that retrieves the names is this:

function get_visitor_names($countryname, $userid){	//Contains the db connection info	include "db.php"; 	$query = "SELECT name FROM visitors WHERE country=? AND userid=?";	$stmt = $db->stmt_init();	$stmt->prepare($query);	$stmt->bind_param('si', $countryname, $userid);	$stmt->execute();	$stmt->store_result();	$stmt->bind_result($name);	$stmt->fetch();	$stmt->close();	$db->close(); 	return $name;}

How does it look?

[JamesB]

ah that's not as bad as i thought, i thought you were retrieving the whole visitors table for each user! at the moment it is sending a query for each user though, it would be faster to just have one query to fetch all the names, skipping the userid= part.i've never used stmt before, but something like this should work:

$names = array(); $query = 'SELECT name FROM visitors WHERE country=?';$stmt = $db->stmt_init();$stmt->prepare($query);$stmt->bind_param('s', $countryname);$stmt->execute();$stmt->store_result();$stmt->bind_result($name);while ($stmt->fetch()) {    $names[] = $name;}$stmt->close();$db->close(); return $names;

that should make the php function return an array of names with syntax:

array(0 => 'name1', 1 => 'name2', etc)

and using that, you wouldn't need the for loop, you'd just call it once:

$names = get_visitor_names($country_name);foreach ($names as $name) {    echo $name;}

[TheGallery]

Oh man that worked like a charm. The page loads instantly I should really get into arrays. One last question (doesn't really worth opening a new thread to ask this), is this method (storing the data retrieved to an array) good for any amount of visitors? I may have 1000 or 10000 or more visitors to retrieve. Will it still be the best method regardless the size?

[JamesB]

it might be faster to skip the array and put "echo $name" where "$names[] = $name" is, that way all the usernames wouldn't need to be stored manually in memory before the echoing starts.

[TheGallery]

it might be faster to skip the array and put "echo $name" where "$names[] = $name" is, that way all the usernames wouldn't need to be stored manually in memory before the echoing starts.

Good point. I was just used to write functions in a separate file and retrieve data from them to the main script, with loops. I will use this method Thanks a lot for the help. You are awesome.