0

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?

1
  • It would be helpful to post the complete plan, along with the actual query and table definitions, because "using index" might mean different things in different contexts. – mustaccio Mar 11 '20 at 19:08
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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