knowledge_seeker knowledge_seeker - 1 month ago 6
MySQL Question

When one select query is made,is there a possibility that the query will take more than one index(either single or composite)

When one select query is made,is there a possibility that the query will take more than one index (either single or composite)?

Answer

As MySQL documentation on How MySQL Optimizes WHERE Clauses says:

Each table index is queried, and the best index is used unless the optimizer believes that it is more efficient to use a table scan.

There is one slight exception to the above rule, if index merge optimisation is used:

The Index Merge method is used to retrieve rows with several range scans and to merge their results into one. The merge can produce unions, intersections, or unions-of-intersections of its underlying scans. This access method merges index scans from a single table; it does not merge scans across multiple tables.

In EXPLAIN output, the Index Merge method appears as index_merge in the type column. In this case, the key column contains a list of indexes used, and key_len contains a list of the longest key parts for those indexes.

However, if your indexes are well thought through, then multi-column indexes cover about 50% of the index merge uses cases. Index merge is not a complete exception to the 1 index rule, since the separate indexes are treated together as if a single index in the process.