Ajax auto complete - how to make it faster?

Question

We are currently running an ajax auto complete script through mySQL, as such:

<?php
$q = strtolower($_GET['term']);
if (!$q) return;
$q = noescape($q);

if (is_numeric($q)){
    $q = mysql_query("SELECT * FROM `blah` WHERE `id` LIKE '" . $q . "%' DESC LIMIT 10");
}else{
    $q = mysql_query("SELECT * FROM `blah` WHERE `name` LIKE '" . $q . "%' DESC LIMIT 10");
}

$json = array();

while ($r = mysql_fetch_array($q)){
    $json[] = array(
        "v"     => $r['v'],
        "p"     => $r['p'],
        "s"     => $r['s'],
        "l"     => $r['v'] . ', ' . $r['s'] . ' (' . $r['p'] . ')'
    );
}

echo json_encode($json);
?>

We are looking to make our search faster, so it doesn't need to do a call to the database and slow things down.

I was looking at someone elses auto complete, and when doing the search - their ajax was making a call to a "search.ds?query=blah" file - what is this? how can we emulate something this fast?

When downloading the .ds file it prompted, we opened it and all it contained was an array of what matched our search, so they aren't storing ALL of the data in there.

Thank you

What is the content-type of the response? – Arun P Johny Jul 12 '11 at 08:31 — Arun P Johny, Jul 12 '11 at 08:31
Content-Type application/json;charset=UTF-8 – Latox Jul 12 '11 at 08:33 — Latox, Jul 12 '11 at 08:33
Set it to `text/json`. Which browser are you using? – Arun P Johny Jul 12 '11 at 08:34 — Arun P Johny, Jul 12 '11 at 08:34

score 6 · Accepted Answer · answered Jul 12 '11 at 08:41

6

If you want to make your autocomplete faster you could do some things:

1) consider if you need a database query (the answer is no if you never or rarely need to change the data for the autocomplete, yes if the data is dynamic like a list of your friends)

2) put covering indexes on the tables/columns you are doing the search on

3) cache your results in the browser to avoid making too much calls

4) NEVER do a select * query!!!You waste a lot of time retrieving data you probably don't use!Just select the field you need!

5) when you do a lookup against an id don't use LIKE as you do: use = it's much faster

answered Jul 12 '11 at 08:41

Nicola Peluchetti

76,206
31
145
192

@Nicola i just want to ask about the (3), that cache your result's in the browsers to avoid making too much call's... Is this will be like the making cache of file and store into the cache folder or just HTTP header i didn't get this – Abdul Muheet Jul 10 '15 at 05:19
1

@Mohit no it means just to cache the AJAX calls in your browser to avoid to make the same query twice. Imagine a user that enters the term foo, then changes it to bar, and then back to foo. If you cache the result of the first "foo" call you don't need to call it again. For caching in the browser, use a javascript object, it doesn't need to be a persistent cache, if the user navigates away or reloads, cache is lost – Nicola Peluchetti Jul 11 '15 at 11:49

score 1 · Answer 2 · edited May 23 '17 at 11:43

1

If you are using internet explorer, it does not know resource with content type application/json. The reason behind this is that Internet Explorer cannot associate the application/json Mime-Type to an application installed on the client-side.

In your case you may set the content type to text/json or just application/json I think.

UPDATED:

You can also refer this, here the answer suggests to use application/json not application/json;charset=UTF-8

edited May 23 '17 at 11:43

Community

1
1

answered Jul 12 '11 at 08:34

Arun P Johny

384,651
66
527
531

I didn't understand a word of that – Latox Jul 12 '11 at 08:38
The application/json;charset=UTF-8 is what was returned on the site I was looking at (the one which returned data back quickly) – Latox Jul 12 '11 at 08:53

score 1 · Answer 3 · answered Jul 12 '11 at 10:06

Turn on gzip compression in this ajax response. This is almost a must because it reduces the response size.
Add database indexes on columns that are used in search query. If the query uses several filtering parameters (ex.: where a = 5 and b = 6) then create composite indexes too. This way the query usually runs faster.
Add caching headers in ajax response with a convenient expiration time. This reduces repeating requests to the web server when the user uses the same search keywords again.
Add server side caching of most popular and most recent requests. This way you will reduce number of database connections/queries when different users uses the same search keywords.
Use fully constructed HTML as an ajax response instead of JSON data. Then on the client side use yourHTMLelement.innerHTML = ajaxResponseData;. This way the browser will not need to parse JSON and construct HTML DOM. This means that data representation will be faster.

this comment is from 12 years ago - is your last bullet point still valid these days? I've never thought about doing it that way... — Scott, Apr 07 '23 at 14:53

Ajax auto complete - how to make it faster?

3 Answers3