In Mysql, why do unused indexes affect the query plan?

Question

I have seen this several times, but I may have misinterpreted the EXPLAIN query plan.

Suppose I have a table (col1, col2). I want to join it with another table on col1 and col2. Therefore, I create an index (col1, col2). Sometimes EXPLAIN indicates that the index is not used. Perhaps some other inefficient index is used or not at all.

But if I create another index (col1), then the first index (col1, col2) is used.

Has this happened to anyone before? Do you have any idea why this might happen?

My theory is that an unused index provides more accurate statistics about a table that alludes to a query plan for using the first index. But I'm not good enough at mysql's inner workings to find out if this is true or how to prove it.

Answer 1

The MySQL documentation for ALTER TABLE indicates that you might need to run ANALYZE TABLE on it in order to update the index power, which I believe is the behavior factor you see. In addition, the query optimizer usually processes empty (or close) empty tables that are completely different from populated tables, and it often performs a full table scan instead of using an index when there are only a few rows. Because of this, I cannot rely on the EXPLAIN output of my dev database.