MySQL Composite Index Failures Explained

There are several reasons why an index may be ineffective when MySQL uses a composite index for querying.

1. Uneven data distribution: If the data distribution in the indexed column is uneven, with some values appearing frequently and others infrequently, MySQL may choose not to use the index for the query. Using the index may require a large amount of disk reads, while not using the index can avoid this overhead.
2. The indexed column is not the first column of the search criteria. In a composite index, MySQL can only use the first column of the index to perform range queries. If the column in the search criteria is not the first column of the index, MySQL may choose not to use the index for the search and instead perform a full table scan.
3. If a function or expression is used in the query conditions, MySQL cannot utilize an index for the query since the results of the function or expression cannot be precomputed and stored in the index.
4. MySQL may not be able to use an index for queries if the data types of the columns in the query criteria do not match the data types of the index columns.
5. Implicit type conversion was performed on the indexed column: If the column in the query condition undergoes implicit type conversion, MySQL may not be able to use the index for the query. For example, if the data type of the indexed column is integer, but the column in the query condition is a string, MySQL may not be able to use the index for the query.
6. Low index selectivity: Index selectivity refers to the ratio of the number of distinct values in an index column to the total number of rows. If the index selectivity is low, meaning there are very few different values in the index column, MySQL may choose not to use an index for queries, as using the index may require a lot of disk reads, while not using the index can avoid this expense.

In conclusion, the main reasons for the failure of MySQL composite index are due to uneven data distribution, the index column not being the first column in the query conditions, the use of functions or expressions, mismatched data types in the index column, implicit type conversion on the index column, or low index selectivity.