Autosuggest with 40 Million Values

Question

I have a DB with 40 million values, and I'm using autosuggest with jquery, and the sentence is getting very slow. I need to know two things: how can I speed up this data (using mysql db with php) and will java with any other db improve speed?

Answer 1

• Be sure to specify the index in the appropriate field. either an exclusive index or the first index segment.
• be sure to use LIKE 'xxx%' (where xxx is still); never LIKE '%xxx%'
• be sure to limit the results of LIMIT 10 in the database, never in the application.

edit : borrowing from bpeterson76 answer :

• do not perform SELECT * , get only the fields you need.
• Do not search one character at a time, wait until you have at least 3 or so.
• Do not search too fast, wait at least 100 ms between requests.

Answer 2

I have one in PHP / MySQL with approximately 2 million records, this is what worked for me:

• I use REGEXP , not like , and it works fine.
• Limit the query to return only the values ​​you need (name, etc.). This seems obvious to most developers, but I have seen select * for things like this in the past.
• Index, index, index. That made the one and only difference I have found so far.
• Limit startup to start with character 3 (for example) instead of character 1.
• Add a delay to autocomplete. If someone prints to clarify the list, there is no reason to shoot until they stop (or slow down) within a reasonable period of time. This can save a lot of hits in db.

I use both the new autocomplete JQuery UI and its predecessor from Jorn on top of bassistance.com , which includes additional features, including caching, which does not make the official release of the user interface. You can give this version a try if it helps.

Answer 3

Make sure you have the correct index as above.

Also, do not delete your database with too many queries. The database is easier to destroy on 100 queries, each of which takes 10 lines, and not 10 queries, each of which takes 10 lines.

So, after the user typed, say, 3 letters, ask for a little more data than you are showing. Let's say you show 10 lines, take 100.

When a user types a different letter, he is likely to use the following 10 values. This way you will send fewer queries to the database.

In the most optimistic case, the user search value will be selected in 1 or 2 queries.

Also, remember the buffer so that you are not forced to resubmit the request when the user deletes the last character.

Answer 4

A few more suggestions ...

• Use SQL_CALC_ROWS in SELECT and then SELECT FOUND_ROWS() if you need to know the total number of elements when you use LIMIT in the same query
• Try and see if you can use Fulltext search (MySql has pretty good support), which is much faster than using expressions like LIKE '%xxx%'
• Use the EXPLAIN command to find out where the bottlenecks are in your query, and to make sure indexes are used
• Make sure the query is a problem, not PHP or anything else (do this by comparing only the queries in which you get poor results)

Answer 5

40 million values

I really can't believe you have so much data and you want to use autocomplete for it.

I use autosuggest with jquery and the sentence becomes very slow.

The returned data must be in MEMORY, because it is much faster than memory. You should use Redis because it is l a quick search in the MEMORY database . The author even wrote a blog post on how to write AutoFill using Redis .

and will java with any other db improve speed?

I would use node.js because it is very fast (but redis is not needed and Java / PHP will work too) and is easy to use.

• Use one of these methods to install node.js + npm => https://gist.github.com/579814 . I really like npm. Especially if I compared (personal) Maven too.
• npm install express hiredis redis