php + mysql - performance - instant auto complete response

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.

Use `Indexing` on Field which you want to show in autocomplete from your Database.

[To see links please register here]

Auto-complete can be triggered when the user input is at-least 3 characters long.

If possible consider to use a nosql database like Mongodb or Cassandra for this table as they are made for that kind of data, especially if the data will grow. To transport the data from the server to the client use json.

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