Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: January 10, 2022 - 6:29 am UTC

Last updated: January 11, 2022 - 3:48 am UTC

Version: mysql 8.0.22

Viewed 1000+ times

You Asked

Does MySql Btree index store NULL value?I execute a sql like "explain select * from t1 where id is null",most id of table t1 is NULL, I think it should use full table scan instead of index scan. But MySql use index. why?

and Chris said...

We specialize in Oracle Database here. If you need detailed help with MySQL, then a forum such as the MySQL Community Space is a better place to ask.

That said, the MySQL docs answer your question:

MySQL can perform the same optimization on col_name IS NULL that it can use for col_name = constant_value. For example, MySQL can use indexes and ranges to search for NULL with IS NULL.

https://dev.mysql.com/doc/refman/8.0/en/is-null-optimization.html

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

MHESH, January 10, 2022 - 7:14 pm UTC

How to check user tables has primary key or not

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.