MySQL IN AND INDEX :)

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).

 

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)

 

Thank you.

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

rep+ for your help

 

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.

 

Yep,

I tried explaining the queries from PHPMyAdmin.

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

Thanks for your help