what does means ref=const,const in mysql explain statment

Question

what does means ref=const,const in mysql explain statment?

i add index for the table and the index contains tow columns where statment contains two index columns and some not in index columns

why does the explain statment extra column show 'Using index' rather than 'Using index condition'(ICP) and why ref column show 'const,const'?

i want to know what's the different between 'ref=const,const' and 'extra=Using index condition'? how does it work and how to verify it

Answer 1

ref=const means that the table has at most one matching row, which is read at the start of the query. Because there is only one row, values from the column in this row can be regarded as constants by the rest of the optimizer. const tables are very fast because they are read only once.

extra=Using index condition means If an index column appears in the query criteria, but some conditions cannot use the index, it will be searched first based on the index's conditions to match the conditions that cannot be used.

You can refer to the official document, which describes how to scan the table when ICP occurs https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html

Answer 2

ref = const,const means that the first two columns of the indicated index (or PRIMARY KEY if it says PRIMARY) were used to find the row (or first of several rows) in the table.

This is usually very efficient, but many other factors may (or may not) lead to a fast (or slow) query execution. Let's see the actual query and the SHOW CREATE TABLE(s) in question. (const,const can show up for either a single table lookup or a JOIN.)

Using index means that the index was "covering". That is, all of the columns needed for the query were found in the one index. This is [usually] the best possible index.

Using index condition (aka "Index Condition Pushdown") indicates that the Index did not have everything it needed. so it had to get the engine (InnoDB) to fetch the row and continue filtering. ICP was a minor improvement added a decade ago; I find it useless in reading an EXPLAIN.

Using filesort is another confusing term. It does not necessarily mean that it had to write data to a temp file. It may have been able to use RAM for the intermediate storage.