Mysql – How to correctly interpret index usages from EXPLAIN of thesql

explainindexMySQL

enter image description here

I've exported the results of an EXPLAIN run on a query. What I find confusing is that there's the key column listing out one of the indexes from the list of possible_keys(not shown in picture) however only the top row makes mention of Using index explicitely in the Extra column.

  1. What does this mean in the 2nd row, is it not using the index listed
    in the key column?

  2. How should I interpret what the contents of the
    key column is about and how it is used?
    enter code here

    a. Should I interpret this as
    that index is used in the where stage of this query?

Best Answer

Using index means that the columns used for that table are all in the chosen index. This is called a "covering index".

Note the terminology confusion: the Key column tells you whether it is "using an index"; Using index says whether that index is "covering" in this usage.

Using index condition is something else; don't get confused.

Using temporary; Using filesort does not necessarily apply to the table in question. See EXPLAIN FORMAT=JSON SELECT ... for specifically which GROUP BY and/or ORDER BY are creating a temp table and/or sorting.

ref=const probably means something like: INDEX(foo) with WHERE foo=123. key_len=8 usually refers to a BIGINT NOT NULL.

ref=Production1_ may be a truncation of Production1_db.my_table.my_column

The second row does not say Using index because the index being used in the second table (FKB8...) is not "covering".

EXPLAIN mostly ignores GROUP BY, ORDER BY, and LIMIT. Instead, it focuses on what index(es), if any, is used for WHERE or ON.

Related Question