SitePoint Sponsor |
|
User Tag List
Results 1 to 4 of 4
-
Oct 6, 2009, 23:55 #1
- Join Date
- Sep 2004
- Location
- Toronto
- Posts
- 795
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Optimizing a LIKE search operation
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
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
-
Oct 7, 2009, 05:21 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
what indexes have you defined?
-
Oct 7, 2009, 09:42 #3
- Join Date
- Sep 2004
- Location
- Toronto
- Posts
- 795
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?I can't believe I ate the whole thing
-
Oct 7, 2009, 11:51 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
that looks okay to me
what does the EXPLAIN on the query show?
Bookmarks