Optimizing a LIKE search operation

[matsko]

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:

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?

[r937]

what indexes have you defined?

[matsko]

The only indexes that are defined are the:

[Locations Table]
ID (primary)
Name (Index)
RegionID (Index)

[Regions Table]
ID (Primary)

Now my search opertion is:

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

Any ideas?

[r937]

that looks okay to me

what does the EXPLAIN on the query show?