I know there is already similar question here like How does the database decide which Index to use. But it seems that those answers are not cool and useless.

In my development and use of MySQL, I found there are multiple factors that affect how MySQL decides which index should be used to execute the SQL statements. Those factors include but are not limited to:

  1. The estimated scan count of rows for certain index.

  2. The order by condition and whether certain index would cause filesort.

  3. The presence of limit 0,N condition and even the value of N would impact the result.

Any response from people who have read the source code or have developed MySQL source codes will be highly appreciated! :)

If you know someone who maybe knows the answers, please share this page with them.

1 Answer 1

  • What is in the WHERE clause.
  • An estimation of how many rows will be fetched -- to decide whether to use an index, or simply scan the table.
  • Size of two tables in a JOIN -- if there is no other good way to decidw which table to start with.
  • The value of N in a LIMIT rarely matters.
  • Whether the columns's datatypes match -- mismatch of COLLATION may inhibit use of an index.
  • Not used: whether the needed blocks is already cached in RAM.
  • Not used: Spinning disk vs SSD. (There is a tunable in 8.0 to help.)
  • In certain situations, an index is dynamically created for a subquery.
  • Size of the index.
  • "Cost" of the various options.
  • ORDER BY is used only if the entire WHERE and GROUP BY are handled first. Or the Optimizer may skip the WHERE and only use an index for the ORDER BY, thereby avoiding 'filesort'.
  • GROUP BY and ORDER BY, of not compatible enough may lead to two filesorts.

See EXPLAIN FORMAT=JSON SELECT ... for some extra clues. See also the "Optimizer trace".

My cookbook approaches your question in a different way... "Given this SELECT, what is the best INDEX for it?"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.