When running EXPLAIN, if the field value for key is not null but Extra is blank, is the key used?

Question

When running this EXPLAIN query without an index

EXPLAIN SELECT exec_date,
    100 * SUM(CASE WHEN cached = 'no' THEN 1 ELSE 0 END)  / SUM(1) cached_no,
    100 * SUM(CASE WHEN cached != 'no' THEN 1 ELSE 0 END)  / SUM(1) cached_yes
FROM requests
GROUP BY exec_date

This is the output

id  select_type table       type    possible_keys   key     key_len ref     rows    Extra
1   SIMPLE      requests    ALL     NULL            NULL    NULL    NULL    478619  Using temporary; Using filesort

If I create an index

ALTER TABLE requests ADD INDEX exec_date(exec_date);

The output is

id  select_type table       type    possible_keys   key         key_len ref     rows    Extra
1   SIMPLE      requests    index   NULL            exec_date   4       NULL    497847  

Since the value of Extra is blank, does that mean the key exec_date is not being used?

On a test server, the execution time of the actual (not the EXPLAIN statement) query with and without the index is the same.

Answer 1

Using index doesn't mean what you think it means. If it is present in the Extra column, it indicates that the optimizer isn't actually reading the entire rows, it is using the index (exclusively) to find column information.

The key could still be in use for other things, for example to perform lookups if you have a WHERE clause etc. In your specific scenario, for example, the disappearance of the Using temporary; actually does mean that your index is being utilized, since MySQL no longer needs to rearrange the contents of your table into a new temporary table to perform the GROUP BY.