autosuggest with 40million values

Question

I have an DB with 40 million values and I am using autosuggest with jquery and the suggestion is getting very slow. I need know two things: how can I speedup with this data (using mysql db with php) and will java with any other db improve the speed?

Answer 1

• be sure to have an index on the matching field. either an exclusive index or the first segment of the index.
• be sure to use LIKE 'xxx%' (where xxx is the input so far); never LIKE '%xxx%'
• be sure to limit the results with LIMIT 10 on the database, never on the app.

edit: borrowing from bpeterson76's answer:

• don't do SELECT *, get only the fields you need.
• don't search single-character, wait until you have at least 3 or so
• don't search too quickly, wait at least 100msec between queries.