0

I have this query:

SELECT *
FROM table1
WHERE foo IN (1,2,3,4) AND bar = 123;

I have 3 indices:

ALTER TABLE table1 ADD INDEX IDX_foo (foo);

ALTER TABLE table1 ADD INDEX IDX_bar (bar);

ALTER TABLE table1 ADD INDEX IDX_foo_bar (foo, bar);

When I try EXPLAIN with the query, IDX_bar is chosen which I don't understand why. So my questions are:

  • Why is IDX_bar chosen but not IDX_foo or IDX_foo_bar?

  • Does MySQL choose index differently depend on the data at the time?

  • Should I only have IDX_bar and remove the other two?

1

1 Answer 1

1

The best index in case above seems to be

ALTER TABLE table1 ADD INDEX IDX_bar_foo (bar,foo);

Why is IDX_bar chosen but not IDX_foo or IDX_foo_bar?

What index will be used and will some index be used at all, is dependent by table statistic (and query text, of course).

Maybe in Your case the index IDX_bar is more selective than another 2 indexes.

Does MySQL choose index differently depend on the data at the time?

That's right. Change data changes statistic and so index choose can change too.

Should I only have IDX_bar and remove the other two?

For optimizing the query You show - yes (if data stat will not change radically). Or remove all 3 indexes and create index shown above.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.