CODEWITHSUNDEEP
javascriptjqueryjquery-uisearchautocomplete
prawn
prawn

Reputation: 2653

search autocomplete performance with many records

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 autocomplete feature attached to this search bar. I've been looking into several ones like jquery autocomplete.

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

So my question is, what is an efficient way to implement a search autocomplete 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 autocomplete control to the returned result set
       });
});

Upvotes: 1

Views: 3336

Answers (4)

Rajab Saleem
Rajab Saleem

Reputation: 9

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);
        }
    });

Upvotes: 1

Nafis Ahmad
Nafis Ahmad

Reputation: 2761

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 complete 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.

Upvotes: 3

MonkeyZeus
MonkeyZeus

Reputation: 20737

Do not bind any events yourself. jQuery Autocomplete 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);
        }
    });
}

Upvotes: 2

Matas Vaitkevicius
Matas Vaitkevicius

Reputation: 61391

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.org/ 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.

Upvotes: 1

Related Questions