To AJAX or not to AJAX

Shawazi

If I have a table with 300 entries, do you believe it is better to have a hidden list loaded everytime a page is loaded so

$result = mysql_query("SELECT name FROM words ORDER BY name");

while ($row = mysql_fetch_array($result))
   echo $row['name']."<br />";

then javascript in a textfield to filter the pre-loaded list

use AJAX for something like:

$result = mysql_query("SELECT name FROM words WHERE name LIKE \"$search%\"ORDER BY name LIMIT 5");

while ($row = mysql_fetch_array($result))
   echo $row['name']."<br />";

So that there is a new query everytime the user presses a key. This makes more queries, but with a smaller result. I believe the first way is more efficient. What do you think?

Piranha

Is apples or pears more tasty?

With the information provided it is impossible to give an answer. Will the table get bigger in the future? How much is returned? How often will users press the key? How long will the initial load take in the first alternative?

Shawazi

Well the table itself now has about 20 entries, but should be considered to be infinitely expandable in the future. So right now, knowing the microtime won't mean much. If the user is going to type "Hello World" and the table has

"Hello Universe"
"Hello World"

As they type, it would check for H-e-l-l-o- -W. Until the W was typed the list would show both entries in a list. So this would send a query for each letter. Meanwhile pulling them all at once, would check the same way, but pull from the existing list.

Piranha

Since you say that it may be infinitely expandable I would defenetley do the AJAX stuff. Then you don't need to do it all over again when it becomes to much to return with one query.

Shawazi

See I'm not sure because then you are repeatedly querying. For instance Facebook does the other method. It has a similar thing and the list is already in the code before you do anything and it's a list of 200-300 people usually. I'll be more specific and take a guess that my table will have 1 thousand tops. I just keep flip-flopping in my mind, maybe you're right.

Piranha

Well, there are always the option to get the next page as well with AJAX, then it would seem to the user that nothing is happening. And there is the possibility to generate a page with all the names, then you don't have to query the database every time. I would probably just try one way and if that doesn't work then try the other.

bubblenut

Whichever sollution you choose to go with allways keep the idea of pregressive enhancement in the forefront of your mind. If the search box is enhanced with AJAX, make it also submitable and make the results from the submit comparable.

aksival

The first method will appear to render results faster on the client side, but will lag each time the page load and the [unlimited] query runs.

You could use AJAX so that every onkeyup event triggers a new, more-specific query, but, chances are, the first letter will be used in a large percentage of the words. So if the query searches for the letter 'A', there is a good chance that most of the words will be returned anyways.

What if you used AJAX to execute the query, but only after a 1 or 2 second delay after the user finished pressing keys? That would eliminate a lot of unnecessary querying.

Or, just ask the people on the Google Suggest project how they're doing it 🙂

bubblenut

Another option as well as a delay is to only make the AJAX call if there are more than n characters in the field. Inccidentally it looks like Google Suggest uses aksival's suggestion. If you go the AJAX route you'll want to optimize the callback script as much as possible. There are two areas I would look at here, firstly the query, you're making a good start by binding the like to the beginning of the field (not putting a % at the start) but also make sure you have an index on that field. The second point is to make the response payload as small as possible, JSON is usually better than XML for this. On a side note, make sure you're escaping your search term properly (check out [man]mysql_real_escape_string[/man])