I have a table of 200k entries with columns of INT's. I want to create an index to make queries faster. This is the query I would like to execute: SELECT A,B,C,D,E FROM table WHERE A=23 and (B=45 or C=43). I created the following indexes: B, ACD, C, ABC.
With the EXPLAIN command I found that MySQL chooses the index ACD. So I kept populating the table with more values and I realized that MySQL was switching between the indexes above (not always the same one).
Since there are many inserts, having various indexes will cause performance issues and we can assume that this table is accessed by other queries that require different columns where every index makes sense.
I am aware of the USE INDEX(), but I would like to understand if we should trust MySQL to choose the right index.
Because of the OR in the SQL statement, MySQL is simply getting the first index that includes A, which is ACD.
I came to the conclusion that the way to solve this issue using an INDEX is to make two separate queries. SELECT A,B,C,D,E FROM table WHERE A=23 AND B=45 which will use the INDEX ABC and then SELECT A,B,C,D,E FROM table WHERE A=23 AND C=43 which will use INDEX ACD. This can all be done in one step with (...) UNION (...), which turns out to be quicker and only uses INDEX's.