0

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.

Improve this question

3 Answers 3

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you Mathew I'll go through that. :)
1

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.

Improve this answer

4 Comments

Thank you Joni. Currently what I do id loading all the names and send it as a json string. But it fails when we have large number of names in the database. Is that the same as your suggestion in caching them.
What fails? Looping through 100,000 names stored in an array may take too long. You should use a data structure appropriate for the task: stevehanov.ca/blog/index.php?id=120
Thanks Joni. Okay I'll check with that. :)
Joni what happens is when loading huge array with thousands of names it slow down the page and most of the time it get crash due to heavy load of array data.
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.

Improve this answer

3 Comments

Thanks @philwilks But if we are accessing server through a very slow network, querying database at every 500ms is not possible and will not return the result immediately.
@Thilanka Not every 500ms. Every time the user presses a key in the input wait 500 ms. If no other key was pressed in that time, retrieve data. If another key was pressed, restart timer.
I admit on all the sites I've implemented this on the database is either on the same server, or a server in the same rack so querying is very quick. @bazmegakapa Thanks for clarifying what I'm talking about!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.