SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    $books++ == true matsko's Avatar
    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
    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?
    I can't believe I ate the whole thing

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    what indexes have you defined?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    $books++ == true matsko's Avatar
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    that looks okay to me

    what does the EXPLAIN on the query show?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •