Description:
mysql> explain select * from nt where i=2 or i is null;
+-------+-------+---------------+------+---------+------+------+--------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+-------+---------------+------+---------+------+------+--------------------------+
| nt | index | i | i | 4 | NULL | 512 | Using where; Using index |
+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
This query does not use "range" query as column i is declared NOT NULL, while it
will use it if I drop NOT NULL from this column definition.
How to repeat:
mysql> create table nt(i int, key(i));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into nt values(1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into nt select i*2 from nt;
Query OK, 1 row affected (0.07 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into nt select i*2 from nt;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into nt select i*2 from nt;
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into nt select i*2 from nt;
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> insert into nt select i*2 from nt;
Query OK, 16 rows affected (0.01 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> insert into nt select i*2 from nt;
Query OK, 32 rows affected (0.01 sec)
Records: 32 Duplicates: 0 Warnings: 0
mysql> insert into nt select i*2 from nt;
Query OK, 64 rows affected (0.02 sec)
Records: 64 Duplicates: 0 Warnings: 0
mysql> insert into nt select i*2 from nt;
Query OK, 128 rows affected (0.03 sec)
Records: 128 Duplicates: 0 Warnings: 0
mysql> insert into nt select i*2 from nt;
Query OK, 256 rows affected (0.03 sec)
Records: 256 Duplicates: 0 Warnings: 0
mysql> explain select * from nt where i=2 or i is null;
+-------+-------+---------------+------+---------+------+------+--------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+-------+---------------+------+---------+------+------+--------------------------+
| nt | range | i | i | 5 | NULL | 10 | Using where; Using index |
+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
mysql> alter table nt change i i int not null;
Query OK, 512 rows affected (0.04 sec)
Records: 512 Duplicates: 0 Warnings: 0
mysql> explain select * from nt where i=2 or i is null;
+-------+-------+---------------+------+---------+------+------+--------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+-------+---------------+------+---------+------+------+--------------------------+
| nt | index | i | i | 4 | NULL | 512 | Using where; Using index |
+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
