CODEWITHSUNDEEP
mysqlindexinginnodb
AkingSSS
AkingSSS

Reputation: 11

Whether null value is stored on B + tree in MySQL？

mysql 5.7 innodb

EXPLAIN SELECT a FROM test WHERE a IS NULL;

a can be null and a is normal index； we can see: Extra info: Using where;Using index; this meaning index store null value?

enter image description here

Upvotes: 0

Views: 1137

Answers (1)

Rick James
Rick James

Reputation: 142298

In analyzing indexing, think of NULL as "just another value".

Your particular SELECT does not do much. It will do an "index scan" since all the columns needed (just a) are in the INDEX inx_a (a) (which you apparently have). That is, the index is "covering". It should not need a "full index scan" (looking at all 'rows').

Yes NULL values will come first in the index; it should perform an "index scan" something like:

  1. Dive into the index's BTree at the beginning.
  2. Read 'rows' until it hits something other than NULL; deliver a.

There are cases where the Optimizer will eschew an obvious INDEX and simply do a "table scan", but that does not seem to be the case here. For example, because most of the rows have a IS NULL, the following is very likely to ignore the index and do a table scan:

SELECT a,b FROM t WHERE a IS NULL

OTOH, INDEX(a,b) would be a "covering index" and it should do a "range scan" in the index.

Please provide SHOW CREATE TABLE, as text not image, if you wish to discuss this further.

Upvotes: 2

Related Questions