I'm developing an application that is essentially a search bar. The source is a SQL table that has about 300,000 records.

Ideally, I would like to have some sort of autoplete feature attached to this search bar. I've been looking into several ones like jquery autoplete.

However, as one can imagine, loading all of these records as the source for the autoplete is impossible. The performance would be abysmal.

So my question is, what is an efficient way to implement a search autoplete feature for a source that contains thousands and thousands of records?

I thought of something like this. Essentially I'm querying the database each time they type something to get a list of results. However, querying a database via ajax doesn't seem optimal.

$( "#search" ).keyup(function( event ) {

      $.ajax({
        //query the database when the user begins typing, get first 1000 records
        //set the source of the autoplete control to the returned result set
       });
});

I'm developing an application that is essentially a search bar. The source is a SQL table that has about 300,000 records.

Ideally, I would like to have some sort of autoplete feature attached to this search bar. I've been looking into several ones like jquery autoplete.

However, as one can imagine, loading all of these records as the source for the autoplete is impossible. The performance would be abysmal.

So my question is, what is an efficient way to implement a search autoplete feature for a source that contains thousands and thousands of records?

I thought of something like this. Essentially I'm querying the database each time they type something to get a list of results. However, querying a database via ajax doesn't seem optimal.

$( "#search" ).keyup(function( event ) {

      $.ajax({
        //query the database when the user begins typing, get first 1000 records
        //set the source of the autoplete control to the returned result set
       });
});

• javascript
• jquery
• jquery-ui
• search
• autoplete

4 Answers 4

You should not start querying the db on very first keyup, (not even in three-four) keyup. For example, User is typing Albatross. When He hit 'A', if you do a Query search it will send you almost 300,000 results right away, cause every set of data must have the letter "A".

So, should ignore first few (3-5) letters. it will be better, if you can store the search keywords. Cache the top results, when 1-3 keyup you show the top search keywords. Auto plete might not be good feature for searching in a that big DB,

Last Tips for the problem, Your users use google and facebook everyday. They are more then 300,000 result for each of search in any of the applications above. Google or facebook does not show 1000 result at once. It is not good for UI Design or your Servers bandwidth. Just think, how can you categorizes and present the data to user, so that they get what they want and you keep your servers bandwidth and processing cost optimal.

always, remember the context.

Do not bind any events yourself. jQuery Autoplete already performs bindings.

The proper way to implement this is to set the source: object to a an AJAX callback:

source: function (request, response) {
    $.ajax({
        url: 'yourQueryUrl.php', // <- this script/URL should limit the number of records returned
        async: true,
        cache: false,
        type: 'GET',
        data: {q: $('#searchBox').val()},
        dataType: 'json',
        success: function (data) {
            response(data);
        }
    });
}

I am assuming you have added indexes to your table, if not that would be your first step, then if performance is insufficient and if your queries often repeat, you might want to look at this. http://memcached/ or some other caching mechanism.

Upon request of some key you would return that key and add it to the cache, opon subsequent request for same key data would be read from cache instead of hitting database. That would reduce the load and increase the speed.

source: function (request, response) {
    $.ajax({
        url: 'yourQueryUrl.php', // <- this script/URL should limit the number of records returned
        async: true,
        cache: false,
        type: 'GET',
        data: {q: $('#searchBox').val()},
        dataType: 'json',
        success: function (data) {
            response(data);
        }
    });