Access mysql database efficiently using ajax(jquery)

Question

I'm working on a php project which uses Mysql, javascrit and jquery as technologies.

In there I need to provide the auto suggestions option to the users when they are typing on the text box to search products by name.

Current implementation loads all the product names from the database as a json string when the page is load and then filter the string according to the inputs. This is inefficient when there are large number of names(about 100,000 Names) available in the database.

I need to change this logic. I need to retrieve the names from the database using ajax requests at the same time when the user is typing on the text box. Ex- Initially no names are available at the suggestion list. Then when user type "A" the application should send a ajax request to the database and then retrieve all the names containing "A" letter and get it as a json string.

But the problem with this method is we have to do lots of database accesses and get the response as the speed of typing to show as suggestions.

Is there any technique which I can use to make efficient this ajax database access. Can someone please help me on this problem.

Thanks in Advance.

Answer 1

I've implemented this sort of thing a few times, and I've found that unless you're dealing with under about 1,000 objects then hitting the database every time via AJAX is the best option.

However I'd definitely recommend waiting until the user pauses briefly before fetching the suggestions. I've found waiting for a pause of about 500ms seconds works well. In other words, instead of hitting the database on every key press, use a JavaScript timer to only fetch the suggestions 500ms after a key is pressed, providing no other key is pressed in that time.

Answer 2

The JQuery UI website provides some auto complete examples that may assist. One of the examples is remote caching:

http://jqueryui.com/demos/autocomplete/#remote-with-cache

If you click on new window on the top right the example will open in a new window where you can copy the source code to your own file and modify as need be.

Answer 3

The best answer depends on the number of users your application has and how often the data changes. What I have done in one occasion is load the full list of names and have the web browser cache it: this way the server is loaded minimally and the suggestions are completed very fast. It works well because the number of unique users is small and the data changes infrequently.

I have an example using Prototype and CakePHP here: http://jonisalonen.com/2011/crazy-fast-ajax-search-suggest-in-cakephp-using-browser-cache/ It shouldn't be too hard to adapt it to jQuery.