Query optimization in massive tables

I have a table containing brick and mortar shops. The table is about 15 million rows with 30 columns.

For now, the query time to retrieve a shop when the user types the name of the shop is about 15 to 20 seconds (we display an autocomplete list so the user can directly select from the list).

I would like to r开发者_如何转开发each a query time of 2-3 seconds so users don't feel frustrated.

What are the actions I should take to reach this goal? (I am currently on Linode with a MySql database... maybe being on Simple DB would help?)

---

If all you are doing is a simple equi-search or prefix-search on a particular field, adding an index on that field should do the trick.

If you are doing anything more complex than that, then you'll have to be aware that there are no silver bullets when it comes to database performance tuning. You'll need to understand both:

• nature of your data (how it is accessed and modified)
• and how indexes and other database techniques actually work under the covers.

For the introduction on the subject, I warmly recommend reading Use The Index Luke.