Why the EXTRA is NULL in Mysql EXPLAIN? Why >= is Using index condition?

Question

mysql> CREATE TABLE `t` (
     `id` int(11) NOT NULL,
     `a` int(11) DEFAULT NULL,
     `b` int(11) DEFAULT NULL,
     PRIMARY KEY (`id`),
     KEY `a` (`a`),
     KEY `b` (`b`)
   ) ENGINE=InnoDB

there is a table named t and it has two indexes named a and b. Insert into t 100000 rows data

mysql> create procedure idata()
  begin
   declare i int;
     set i=1;
     while(i<=100000)do
       insert into t values(i, i, i);
       set i=i+1;
     end while;
   end;
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> call idata();

I do some experiments, some are as follows

Now, i want to know;

(1)why explain select * from t where a >= 90000; extra is Using index condition? it has index key, but it doesn't have index filter and table filter, so why it is Using index condition?

(2)why explain select * from t where a = 90000; extra is NULL? is needs to have an access to the table,if the first case is Using index condition, why the second can't be Using index condition?

(3)why explain select a from t where a >= 90000; extra is Using where; Using index? i know it uses cover index, so extra has Using index;but why extra has Using where? it means server needs to filter the data? but storage engine has already return the correct, why server needs to filer?

Answer 1

First, terminology...

"Using index" means that the (in this case) INDEX(a) contains all the columns needed. That is "the index is covering".

"Using index condition" is quite different. Internally, it is called ICP (Index Condition Pushdown). This refers to whether the "handler" checks the expression or whether the "condition" (a >= 90000) is handed off to the Engine (InnoDB) to do the work.

As for "Using where"; that is still a mystery to me, even after using MySQL for 20 years and looking thousands of Explains. I ignore it.

In all 3 of your cases, INDEX(a) is used. This is indicated primarily by "key" ("a"--the name of the key, not the column), "key_len" ("5": 4-byte INT plus 1 for NULLable), and secondarily by "type" (which does not say "All").

Further

• If you change the 90000 to 70000, you may find that it will switch to a table scan. Why bounce back and forth between the Index's BTree and the data's BTree (via the PRIMARY KEY). The Optimizer will assume that it will be faster to simply scan all the table, ignoring the rows that fail the WHERE clause.

• EXPLAIN FORMAT=JSON SELECT -- Gives you a lot more information. (Perhaps not much more info for this simple query.) One useful surprise is that it will show how many sorts the single mention of "filesort" really refers to. (A possibly easy way to make this happen is GROUP BY x ORDER BY y; that is group and order by different columns.)

• Explain rarely has such clean numbers, like your "10001". Usually, the "rows" columns is an approximation, sometimes a terrible approx.

• The slowlog records "Rows examined"; it will probably say 10001 (or maybe only 10000) and 1 for your tests. For a table scan, it would be a full 100K.

• Another way to get "Rows examined" is via the "Handler" STATUS values. See http://mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts