|
Take a dataset and settings from MDEV-4362.
Then lets add another column with another distribution:
alter table t5 add col2 int;
|
update t5 set col2=NULL where col2 < 33;
|
update t5 set col2=NULL where col1 < 33;
|
update t5 set col2=178 where col1 >= 33 and col1 < 66;
|
update t5 set col2=47 where col1 >= 66 and col1 < 77;
|
set @a=11;
|
update t5 set col2=(@a:=@a+1) where col1 >= 77;
|
analyze table t5 persistent for all;
|
Let's first check for NULLs:
MariaDB [j10]> explain extended select * from t5 where col2 IS NULL;
|
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
|
| 1 | SIMPLE | t5 | ALL | NULL | NULL | NULL | NULL | 10000 | 33.00 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
|
1 row in set, 1 warning (0.00 sec)
|
Correct.
MariaDB [j10]> explain extended select * from t5 where col2 IS not NULL;
|
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
|
| 1 | SIMPLE | t5 | ALL | NULL | NULL | NULL | NULL | 10000 | 65.01 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
|
1 row in set, 1 warning (0.00 sec)
|
Also correct, a precise estimate.
MariaDB [j10]> explain extended select * from t5 where col2 IS not NULL or col2 is null;
|
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
|
| 1 | SIMPLE | t5 | ALL | NULL | NULL | NULL | NULL | 10000 | 65.01 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
|
1 row in set, 1 warning (0.00 sec)
|
Ooops. Why is selectivity still 65% ?
|