SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    524
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Speeding up queries with combined indexes

    Does it speed up queries to use a combined index on the fields you're searching one, rather than indexes on each field?

    eg if we were to query with the condition:
    Code:
    SELECT * FROM my_table WHERE col1 < 100 AND col2>300
    If we have an index on col1 and and index on col2, the optimiser would only use one of the indexes, would it not? however if we added
    Code:
    alter my_table add index combined_index(col1,col2)
    The optimiser seems to prefer this joined index. I haven't run any benchmarks, but is this the way to go? Utilising a combined index for a combination of fields you are likely to use in your condition? Maybe there's a better term for it that I could look up.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    yep, that's the way to go

    and of course if you have an index on (col1,col2), then you don't need a separate one on col1 alone

    i believe the term is compound index
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    524
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Good stuff. i reckon this'll come in really useful in tables with loads of data.
    if you have an index on (col1,col2), then you don't need a separate one on col1 alone
    How about if I had an index on (col1, col2......col15), would that index see me right if I was searching on col3 AND col5 AND col7 etc?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    no, it wouldn't be used, since col3 isn't the first column in the index

    indexes are used only starting at the leftmost column declared in the index, and proceeding to the right
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    524
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Cheers for the info. I feel empowered now.


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
  •