Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment