MySQL IN AND INDEX :)

Discussion in 'MySQL' started by thuankkk, Feb 9, 2009.

0
  1. #1
    Hi,

    I have a query like this:
    So, which index should I create, 1 index for each column, or index for three collumn? (I also query on one collumn).
     
    thuankkk, Feb 9, 2009 IP
  2. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    If you are always selecting by these three criteria, I would use an index on all three columns.

    INDEX multi_index (firstname, lastname, age)

    If you plan on querying using only one of the columns or if you plan on using OR (ex: firstname IN('Tom', 'Mary', 'Joe') OR lastname IN('Cruise', 'Freeman', 'Peterson'))

    Then you will want 3 separate indexes.

    INDEX firstname (firstname)

    INDEX lastname (lastname)

    INDEX age (age)
     
    jestep, Feb 9, 2009 IP
    thuankkk likes this.
  3. thuankkk

    thuankkk Active Member

    Messages:
    503
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    95
    #3
    Thank you.

    I must use AND for this query, so I create an index for 3 cols, is it right?

    rep+ for your help :)
     
    thuankkk, Feb 9, 2009 IP
  4. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #4
    Correct. Also I would check with each method using explain.

    For the tri-index to work, all of the columns must be in the WHERE clause. If they're not, you should index each column independently.
     
    jestep, Feb 10, 2009 IP
  5. thuankkk

    thuankkk Active Member

    Messages:
    503
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    95
    #5
    Yep,

    I tried explaining the queries from PHPMyAdmin.

    Btw, I created both tri-col index, and index for each col :D

    Thanks for your help :)
     
    thuankkk, Feb 11, 2009 IP