Optimizing a LIKE search operation
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:
This works, but once again its very slow (think about how many columns need to be combined with the regions).
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
There are 2.6 million locations and about 3,000 regions.
Any ideas as to how to optimize the speed of the search?