SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2004
    Location
    uk
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Speed of query, Indexing problem with OR query

    The question is how to speed up a select query that is looking for a value that is in one field or another - the problem seems to be that the indexes are not being used and that a table scan is being done every time. The exact problem and most of what I have tried to solve it is detailed here. I could really do with some help on this!!

    I have two fields area and town and I need to search if the location is in either of these fields.

    The way the data is structured is that if no area was specified then area contains the same information as town. I cannot change this data setup. But I could add an extra field to say if the two fields are the same or different.

    The table is about 1million rows.

    I have indexes for area,town and area+town.

    A search to return all rows 'where area like location%'' takes .03secs, and the same to find all rows where it is in town. Using explain reveals that the index used is area+town in the first case and town in the second. Queries used are:

    PHP Code:
    SELECT FROM table WHERE area LIKE 'location%' 
    PHP Code:
    SELECT FROM table WHERE town LIKE 'location%' 
    However when I search for all rows with location in area OR town using any of the following queries no index is used and the query takes 4+ seconds because it does a full table scan and does not use an index. This applies even using FORCE INDEX.

    So none of the following use an index and all of them are really slow:

    PHP Code:
    SELECT FROM table WHERE (area or townLIKE 'location%' 
    PHP Code:
    SELECT FROM table WHERE area LIKE 'location%' OR town LIKE 'location%' 
    PHP Code:
    SELECT FROM table FORCE INDEX(have tried all of themWHERE area LIKE 'location%' OR town LIKE 'location%' 
    How can I use an index?
    Is that the wrong approach - should I be creating another field/s and using them?

    Any suggestions greatfully received.

    Thanks
    Steve
    When all else fails, persistence prevails
    UK House Prices

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    adding a column is not the right answer, not even as a last resort

    have you tried this --
    Code:
    select *
      from table 
     where area like 'location%' 
    union 
    select * 
      from table 
     where town like 'location%'
    or this --
    Code:
    select *
      from yourtable
     where pkey in
           (
           select pkey 
             from yourtable 
            where area like 'location%' 
           union 
           select pkey 
             from yourtable 
            where town like 'location%' 
           )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Jan 2004
    Location
    uk
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Fantastic!!!

    r937 it is no wonder you are the database guru - the first solution worked perfectly.

    Am now working on which order they should be in there are always far fewer occurences in area than town, - any thoughts?

    Regards
    Steve
    When all else fails, persistence prevails
    UK House Prices

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i don't think it matters which column has fewer values, if you have to look in both anyway
    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
  •