Search code examples
mysqlsqlsql-execution-planmysql-5.5

mysql execution plan explanation

There is following query

select count(*)
from my_table my_t
where my_t.c1 = '3' and my_t.c2 = '123'

This table has index on (c1, c2) and both these columns have not null constraint.

Its execution plan:

# id, select_type, table, type, possible_keys,    key, key_len,         ref, rows, Extra
   1,      SIMPLE,  my_t,  ref,        my_idx, my_idx,     157, const,const,    1, Using where; Using index

It uses where, but for what purpose? As both columns are not null then index will definitely contain all records and that is necessary just to count them.

Am I missing something?

Solution

  • The documentation says:

    Using index

    The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.

    If the Extra column also says Using where, it means the index is being used to perform lookups of key values. Without Using where, the optimizer may be reading the index to avoid reading data rows but not using it for lookups. For example, if the index is a covering index for the query, the optimizer may scan it without using it for lookups.

    As far as I understand from the above, Using index only means that reading the index specified in the key column is enough to get all the data needed to compute the result; there is no need to read the table data. It doesn't say how the index information is used.

    Using where is the one that says the values read from the index mentioned in the key column are compared against the values of the columns or constants indicated in the ref column of the output. The ref column contains const, const because the values use to compare against are string constants ('3' and '123').