In Mysql, why do unused indexes affect the query plan?
mysql
Solution
The documentation of MySQL for `ALTER TABLE` states that it may be required to run `ANALYZE TABLE` on it to refresh the index cardinality, which I believe to be a factor in the behaviour you're seeing. Also, the query optimiser usually handles empty (or near) empty tables quite different from populated tables, and it'll often do a full table scan instead of using an index when there are only a few rows. For my own development at `$work` I can't rely on the `EXPLAIN` output of my dev database because of that.
Problem
I've seen this several times but I could be misinterpreting the EXPLAIN query plan. Suppose I have a table(col1, col2). I want to join it with another table on both col1 and col2. So I create an index(col1, col2). Sometimes, the EXPLAIN shows that the index is not being used. Perhaps some other inefficient index is used or none at all. But if I create another index(col1), then the first index(col1, col2) is used. Has anyone ever had this happen to them before? Do you have any idea why this might happen? My theory is that the unused index provides some more accurate statistics about the table that hints to the query plan to use the first index. But I'm not familiar enough with the inner workings of mysql to know if this is true or how to prove it.