1

I want to make a search suggestion like Google that's fast. I tried it with AJAX/jQuery, but its speed was very slow. I also tried with XML instead of MySQL, but it was slow too. How can I speed it up? my jquery code is:

function lookup(inputString,fname,sbox,asbox) {
    if(inputString.length == 0) {
        // Hide the suggestion box.
        $('#'+sbox).hide();
    } else {
        // post data to our php processing page and if there is a return greater than zero
        // show the suggestions box
        $.post(fname, {mysearchString: ""+inputString+""}, function(data){
            if(data.length >0) {
                $('#'+sbox).show();
                $('#'+asbox).html(data);
            }
        });
    }
}

and php code is

query : SELECT maincatid,category FROM maincategory WHERE category LIKE '%$mysearchString%' LIMIT 10"

        if($rs) {
            while ($result = @mysql_fetch_object($rs)) 
            {
                echo '<li onClick="fill(\''.$result->maincatid.'\',\''.$result->category.'\');">'.str_ireplace($mysearchString,'<span style="background-color:#C1E0FF;">'.$mysearchString.'</span>',$result->category).'</li>';
                $c++;
            }
        } 
        else {
            echo 'ERROR: There was a problem with the query.';
        }
    }
    } else {
    } 
} else {
    echo 'Access denied.';
}
Peter Hosey
  • 95,783
  • 15
  • 211
  • 370
Deepa
  • 1,201
  • 5
  • 15
  • 23
  • what google does is it does not send ajax request on the front page. instead it has an array of popular searches. which is displays using autocomplete. They sure have lots of data, and they know what people search. however, if you want to make your suggestion fast, then you have to optimize your query. – S L Mar 08 '11 at 05:02
  • @experimentX: try it and see - google **does** perform an ajax request for each keystroke on query input. – zerkms Mar 08 '11 at 05:03
  • i think google is use another technique not sends an ajax request – Deepa Mar 08 '11 at 05:10
  • 1
    @Deepa: is it so hard to open google.com and see? They **do use regular ajax requests** – zerkms Mar 08 '11 at 05:13
  • @experimentX:,@dagon i submit my code . plz tell me how i optimize my query – Deepa Mar 08 '11 at 05:16
  • just curious, why have you put `@` in fornt of `mysql_num_rows`, `mysql_fetch_object`, `mysql_free_result`, `mysql_close` ... – zerkms Mar 08 '11 at 05:17
  • 3
    `LIKE '%$mysearchString%'` -- this stuff cannot be optimized. Google about fulltext search or sphinx + read something about sql injections – zerkms Mar 08 '11 at 05:17
  • @zerkms: what is substitution of like query in term of speed optimization. – Deepa Mar 08 '11 at 05:30
  • @zerkms I am seeing any request on the main page on http://google.com.np ! but on the other pages it does send an ajax request – S L Mar 08 '11 at 05:31
  • @experimentX: what *other* pages? It performs ajax queries for search suggestions. – zerkms Mar 08 '11 at 05:35
  • @Deepa: have you tried to google the keywords I gave you or you think that we'll do your work for you? – zerkms Mar 08 '11 at 05:36
  • @zerkms i m studying about it right now – Deepa Mar 08 '11 at 05:44
  • @zerkms then why am i not being able to see them on my firebug console? – S L Mar 08 '11 at 05:46
  • @experimentX: I have no any idea what pages you're experimenting at. At google.com there is a separated request per each keystroke. – zerkms Mar 08 '11 at 05:47
  • @zerkms this stupid google is redirecting me at google.com.np and here there are no ajax request – S L Mar 08 '11 at 05:56
  • @experimentX: there are ajax requests on google.com.np: http://img4.imageshack.us/img4/4240/suggesty.png – zerkms Mar 08 '11 at 05:58
  • @zerkms: I agree with you .. since i forgot to click 'All' on my net tab i couldn't see it, but how come i not see in my console? whenever i make request using $.get or $.post i usually see it here? – S L Mar 08 '11 at 06:02
  • @experimentX: i don't know why they don't appear on console, but they do on Net ;-) Indeed it is strange but there're still ajax requests on each keystroke. – zerkms Mar 08 '11 at 06:04
  • @zerkms yes sure you were resourceful. maybe if i could find some articles on net – S L Mar 08 '11 at 06:09
  • @zerkms i think they are not ajax request afterall, the are http request like we do using jQuery `$(img).attr = something` we can load image. Now how it does, i don't know. If i find it i will let you know. – S L Mar 08 '11 at 13:49
  • @experimentX: does it matter how is it performed? To be ajax the request need to be done asynchronously by javascript and get xml (json) as a result. – zerkms Mar 08 '11 at 13:59

3 Answers3

1

I would suggest that you profile to see where your greatest time is spent. I like to watch packets go from Wireshark to the their destination, although that seems like overkill when there are plugins for Firefox that will trace your requests.

However, looking at your code, I would say that most likely, it is the server-side code, specifically the WHERE x LIKE %y% clause. Most DBs don't do as well with a left wildcard because they use a b-tree for their indices, so I would get rid of that first wildcard. Beyond that, pre-compile a list of suggestions based on past misspellings and their corrections. If you did this and properly indexed your table, your requests could be quite snappy.

Noel
  • 119
  • 3
0

There are lots of reasons google's search results will be faster than yours.

Off the top of my head,

  1. They would have some sort of optimized data warehouse with indexes and full text search instead of the free version of MySQL

  2. They don't have just one server that serves their request, they'd have a CDN and server clusters and you'd hit the server nearest you for low latency

  3. They'd have the current trending queries cached so they wouldn't have to look it up everytime you hit it.

  4. They'd be running on their own dedicated servers that do nothing but serve your search results every day instead of a shared server with a bunch of other people on it

Forget about trying to match google. Find out where your search is slow and fix that. Install firebug and see how long your server takes to respond. Profile your code to see what parts are slow. Most of the time it's badly designed tables and slow servers that are the cause

JohnP
  • 49,507
  • 13
  • 108
  • 140
  • Non free mysql is absolutely the same as free one. So I wouldn't emphasize on this fact. CDN is for static content, seems like you wanted to say that they have *a cluster*. – zerkms Mar 08 '11 at 05:39
  • @zerjms I meant that literally, they don't run mysql at all. Wasn't comparing the community version with the paid (support) version. Yup, you're right, cluster should be added to clarify. Done! – JohnP Mar 08 '11 at 05:43
  • then mention a `big table` http://en.wikipedia.org/wiki/BigTable in the 1st item, since that is what they use ;-) – zerkms Mar 08 '11 at 05:51
0

Most likely you want to use an elite data structure like a binary tree or a trie, or a radix-tree, or patricia-tree or crit-bit tree. I did myself an implementation of a kart-trie. You can grab it at phpclasses.org and search for kart-trie.

Saeed
  • 7,262
  • 14
  • 43
  • 63
Micromega
  • 12,486
  • 7
  • 35
  • 72
  • why do I get a negative vote? isn't it true? isn't it allowed to point to ressources? – Micromega Mar 08 '11 at 08:17
  • here is a link to a php-trie phpir.com/tries-and-wildcards! – Micromega Mar 08 '11 at 08:39
  • I didn't downvote but most likley: u != you, there is no such thing as an "elite" data structure, and most of all, any of those datastructures would be implemented at the database level. They are not useful when doing a full text search. – Byron Whitlock Mar 08 '11 at 22:23
  • dude, i don't think we have anything to discuss and also it is wrong to implement a trie in database level because in database level there isn't such a datastructure. – Micromega Mar 09 '11 at 00:20
  • Isn't the same like this problem? Without the puzzle? http://stackoverflow.com/questions/746082/how-to-find-list-of-possible-words-from-a-letter-matrix-boggle-solver – Micromega Mar 10 '11 at 17:13