How MySQL decide which index to use

No tldr this time -_-!!!

Concepts

• Without an index, MySQL must begin with the first row and then read through the entire table

• Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees

  • Sorted

  • Fast lookup for exact matches (= operator)

  • Fast lookup for ranges (for example, >, <, and BETWEEN operators)

  • Available for most storage engines, such as InnoDB and MyISAM

Flight Manual

• Matching a WHERE clause

• For multiple indexes, uses the index that finds the smallest number of rows

• For multiple-column index, uses leftmost prefix. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3)

• Involving comparison

  • Table joins, more efficiently if columns are the same type and size. In this context, VARCHAR and CHAR are considered the same
  • String columns, should use the same character set. For example, comparing a utf8 column with a latin1 column precludes use of an index
  • Dissimilar columns (comparing a string column to a temporal or numeric column, for example) may prevent use of indexes if values cannot be compared directly without conversion. For a given value such as 1 in the numeric column, it might compare equal to any number of values in the string column such as '1', ' 1', '00001', or '01.e1'. This rules out use of any indexes for the string column

• MIN() or MAX() value

• To sort or group a table if the sorting or grouping is done on a leftmost prefix of a usable index

• Covering index

  • For instance, this:

    SELECT *
    FROM tablename
    WHERE criteria
    

    will typically use indexes to speed up the resolution of which rows to retrieve using criteria, but then it will go to the full table to retrieve the rows.

    However, if the index contained the columns column1, column2 and column3, then this sql:

    SELECT column1, column2
    FROM tablename
    WHERE criteria
    

    and, provided that particular index could be used to speed up the resolution of which rows to retrieve, the index already contains the values of the columns you’re interested in, so it won’t have to go to the table to retrieve the rows, but can produce the results directly from the index.

References

MySQL :: MySQL 8.0 Reference Manual :: 8.3.1 How MySQL Uses Indexes

What is a Covered Index

Speed up your queries using the covering index in MySQL