Results 1 to 4 of 4
Oct 6, 2009, 22:55 #1
- Join Date
- Sep 2004
- 0 Post(s)
- 0 Thread(s)
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:
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?I can't believe I ate the whole thing