How MySQL chooses between indexes

from the Artful MySQL Tips List

The optimiser looks to minimise the number of rows that must be read, so it tries to estimate from each index how many rows must be read. If those estimates are equivalent, it chooses the smaller of the two indexes.

When the query specifies a range of values, the storage engine tries to compute the number of rows in that range. It can do so for single equality conditions and ANDed multiple conditions, starting with the first index column and proceeding to the last, or to the first index column for which a non-equality condition has been specified. In the case of InnoDB, it finds the B-tree start and end entries, and estimates distance between them as a rowcount.

Last updated 16 Aug 2024

Return to the Artful MySQL Tips page