Mysql - optimal indexing for query using IN operator 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?
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.