Antijoin optimization sample with NOT NULL table.sql

Antijoin optimization sample with NOT NULL table.sql

--------------------------------------------
-- Definition
--------------------------------------------
mysql> create table not_nulltest (c1 int not null);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into not_nulltest(c1) values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from not_nulltest
    -> ;
+----+
| c1 |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

--------------------------------------------
-- NOT IN(subquery)
--------------------------------------------
mysql> select * from not_nulltest where c1 not in (select * from not_nulltest where c1 > 1);
+----+
| c1 |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> explain format=tree select * from not_nulltest where c1 not in (select * from not_nulltest where c1 > 1)\G
*************************** 1. row ***************************
EXPLAIN: <not executable by iterator executor>

1 row in set (0.01 sec)


--------------------------------------------
-- Add index
--------------------------------------------

mysql> alter table not_nulltest add index idx(c1);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain format=tree select * from not_nulltest where c1 not in (select * from not_nulltest where c1 > 1)\G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop anti-join  (cost=1.60 rows=3)
    -> Index scan on not_nulltest using idx  (cost=0.55 rows=3)
    -> Filter: (not_nulltest.c1 > 1)  (cost=0.28 rows=1)
        -> Index lookup on not_nulltest using idx (c1=not_nulltest.c1)  (cost=0.28 rows=1)

1 row in set (0.00 sec)