Ajax auto complete - how to make it faster?

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开发者_JAVA百科);
?>

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

---

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

---

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

---

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