Autocomplete, javascript & hitting a database ... i want your input

You guys who have used auto-complete with jquery, or yui… How did you go about the database part?

When one builds an auto complete feature for a website, how many queries are usually sent to the database?

For example, suppose I type “q” in a text box

I could issue a query like

Select * FROM names where name like ‘q’;

Then for the next letter “u”

"Select * FROM names where name like ‘qu’;

I could run a query for every letter that is inputted in the text box, but a database transaction for every letter seems very costly.

The best auto-complete solution to me, seems to dump a database into a big object, and just search object with JavaScript.

I want your input. How do you implement the data retrieval of auto-complete.

Storing everything in one big object may work while the database isn’t very big, but once it does get big this will slow down the page considerably, because this whole object has to be downloaded.

As for the AJAX, you could do a few things:

  1. Set a minimum number of characters before running the AJAX, e.g. don’t autocomplete for just one character, but only start when there are at least two or three
  2. Don’t fire an AJAX query immediately when a user types, but set a timout to fire it in say 500ms. When the user types something else, cancel the previous timeout and start a new one for the new string. That way you only fire an AJAX request when the user stops typing, and not for every letter he types (which may be annoying for the user as well, too much going on).