Menu
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.
556
A wrong index can be an index created on a column that doesn't provide easier data manipulation or an index created on multiple columns which instead of speeding up queries, slows them down. A table without a clustered index can also be considered as a poor indexing practice.
Having two identical indexes makes a negative impact on the performance of SQL queries. It is actually a waste of disk space and also slows down the insertions to the table. Therefore, it is a good practice to avoid duplicate indexes to eliminate these issues.
Indexes are one of the most important features of the SQL Server while being most overlooked and misused in practice. Their proper use leads to great server performance, but having them set up incorrectly or not having them set up at all, leads to poor execution times.
A useful SQL Server index enhances the query and system performance without impacting the other queries. On the other hand, if you create an index without any preparation or consideration, it might cause performance degradations, slow data retrieval and could consume more critical resources such as CPU, IO and memory.
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.
130
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
