Hey guys.

I'm setting up a locations autocomplete feature on my website. Basically I'm scanning a table consisting of 2.6 million records (all the cities in the world) for a string based match (WHERE location LIKE "abc%").

The operation is very slow since the record set has to join itself with the regions table.

Here's the full query:

Code:
SELECT l.id,l.name,r.name as region FROM locations l INNER JOIN regions r ON l.region_id=r.id WHERE l.name LIKE "abc%" ORDER BY l.name LIMIT 10
This works, but once again its very slow (think about how many columns need to be combined with the regions).

There are 2.6 million locations and about 3,000 regions.

Any ideas as to how to optimize the speed of the search?