php + mysql - 性能 - 即时自动完成响应

Question

I'm building an autocomplete feature for an input box which is similar to Google's Instant Search.

I need to have an instant response for the autocomplete feature. The table I'm executing the query with the term in it has hundreds of thousands of records and more that a hundred of columns. For example: 500,000 records, 120 columns.

So, I have two issues:

1. Executing a query with the most possible speed.
2. Fast response on showing the result in search box's auto complete drop down.

By the way, I'm using the Joomla framework which might cause some slow down, too.

Answer 1

1. Create indexes on some necessary sql fields.
2. Use delay depending on the number of key pressed (and also, the time between 2 key pressed if you want).
3. Add your own cache logic.
4. LIMIT the result of the query