Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Done
Description
See https://antognini.ch/2017/12/how-well-a-query-optimizer-handles-subqueries/
Summary of comparision of MariaDB vs posted MySQL results
- E18 - is converted into semi-join
- E28, E29 - EXISTS-to-IN conversion works, the query plan is now good
- F28, F29 - Materialization is now applied, the query plan becomes good
- means the query wont be faster on this particular dataset, but the query plan becomes good.
Details
A10:
explain
|
SELECT * FROM small WHERE u = (SELECT nu FROM large WHERE u = 6);
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|
| 1 | PRIMARY | small | const | small_u | small_u | 4 | const | 1 | |
|
| 2 | SUBQUERY | large | const | large_u | large_u | 4 | const | 1 | |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|
A11:
explain
|
SELECT * FROM small WHERE n = (SELECT n FROM large WHERE u = 6);
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
| 1 | PRIMARY | small | ref | small_n | small_n | 5 | const | 1 | Using where |
|
| 2 | SUBQUERY | large | const | large_u | large_u | 4 | const | 1 | |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
A12:
explain
|
SELECT * FROM small WHERE n = (SELECT nn FROM large WHERE u = 6);
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
| 1 | PRIMARY | small | ref | small_n | small_n | 5 | const | 1 | Using where |
|
| 2 | SUBQUERY | large | const | large_u | large_u | 4 | const | 1 | |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
A13:
explain
|
SELECT * FROM small WHERE nn = (SELECT n FROM large WHERE u = 6);
|
+------+-------------+-------+-------+---------------+----------+---------+-------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+----------+---------+-------+------+-------------+
|
| 1 | PRIMARY | small | ref | small_nn | small_nn | 4 | const | 1 | Using where |
|
| 2 | SUBQUERY | large | const | large_u | large_u | 4 | const | 1 | |
|
+------+-------------+-------+-------+---------------+----------+---------+-------+------+-------------+
|
A14:
explain
|
SELECT * FROM small WHERE nn = (SELECT nn FROM large WHERE u = 6);
|
+------+-------------+-------+-------+---------------+----------+---------+-------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+----------+---------+-------+------+-------------+
|
| 1 | PRIMARY | small | ref | small_nn | small_nn | 4 | const | 1 | Using where |
|
| 2 | SUBQUERY | large | const | large_u | large_u | 4 | const | 1 | |
|
+------+-------------+-------+-------+---------------+----------+---------+-------+------+-------------+
|
A20:
explain SELECT * FROM large WHERE u = (SELECT nu FROM small WHERE u = 6);
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|
| 1 | PRIMARY | large | const | large_u | large_u | 4 | const | 1 | |
|
| 2 | SUBQUERY | small | const | small_u | small_u | 4 | const | 1 | |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|
A21:
explain SELECT * FROM large WHERE n = (SELECT n FROM small WHERE u = 6);
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
| 1 | PRIMARY | large | ref | large_n | large_n | 5 | const | 1 | Using where |
|
| 2 | SUBQUERY | small | const | small_u | small_u | 4 | const | 1 | |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
A22:
explain SELECT * FROM large WHERE n = (SELECT nn FROM small WHERE u = 6);
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
| 1 | PRIMARY | large | ref | large_n | large_n | 5 | const | 1 | Using where |
|
| 2 | SUBQUERY | small | const | small_u | small_u | 4 | const | 1 | |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
A23:
explain SELECT * FROM large WHERE nn = (SELECT n FROM small WHERE u = 6);
|
+------+-------------+-------+-------+---------------+----------+---------+-------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+----------+---------+-------+------+-------------+
|
| 1 | PRIMARY | large | ref | large_nn | large_nn | 4 | const | 1 | Using where |
|
| 2 | SUBQUERY | small | const | small_u | small_u | 4 | const | 1 | |
|
+------+-------------+-------+-------+---------------+----------+---------+-------+------+-------------+
|
A24:
explain SELECT * FROM large WHERE nn = (SELECT nn FROM small WHERE u = 6);
|
+------+-------------+-------+-------+---------------+----------+---------+-------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+----------+---------+-------+------+-------------+
|
| 1 | PRIMARY | large | ref | large_nn | large_nn | 4 | const | 1 | Using where |
|
| 2 | SUBQUERY | small | const | small_u | small_u | 4 | const | 1 | |
|
+------+-------------+-------+-------+---------------+----------+---------+-------+------+-------------+
|
B10:
explain SELECT * FROM small WHERE u != (SELECT nu FROM large WHERE u = 6);
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
| 1 | PRIMARY | small | range | small_u | small_u | 4 | NULL | 9 | Using where |
|
| 2 | SUBQUERY | large | const | large_u | large_u | 4 | const | 1 | |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
B11:
explain SELECT * FROM small WHERE n != (SELECT n FROM large WHERE u = 6);
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
| 1 | PRIMARY | small | ALL | small_n | NULL | NULL | NULL | 10 | Using where |
|
| 2 | SUBQUERY | large | const | large_u | large_u | 4 | const | 1 | |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
B12:
explain SELECT * FROM small WHERE n != (SELECT nn FROM large WHERE u = 6);
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
| 1 | PRIMARY | small | ALL | small_n | NULL | NULL | NULL | 10 | Using where |
|
| 2 | SUBQUERY | large | const | large_u | large_u | 4 | const | 1 | |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
B13:
explain SELECT * FROM small WHERE nn != (SELECT n FROM large WHERE u = 6);
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
| 1 | PRIMARY | small | ALL | small_nn | NULL | NULL | NULL | 10 | Using where |
|
| 2 | SUBQUERY | large | const | large_u | large_u | 4 | const | 1 | |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
B14:
explain SELECT * FROM small WHERE nn != (SELECT nn FROM large WHERE u = 6);
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
| 1 | PRIMARY | small | ALL | small_nn | NULL | NULL | NULL | 10 | Using where |
|
| 2 | SUBQUERY | large | const | large_u | large_u | 4 | const | 1 | |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
B20:
explain SELECT * FROM large WHERE u != (SELECT nu FROM small WHERE u = 6);
|
+------+-------------+-------+-------+---------------+---------+---------+-------+--------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+--------+-------------+
|
| 1 | PRIMARY | large | range | large_u | large_u | 4 | NULL | 494636 | Using where |
|
| 2 | SUBQUERY | small | const | small_u | small_u | 4 | const | 1 | |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+--------+-------------+
|
B21:
explain SELECT * FROM large WHERE n != (SELECT n FROM small WHERE u = 6);
|
+------+-------------+-------+-------+---------------+---------+---------+-------+--------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+--------+-------------+
|
| 1 | PRIMARY | large | ALL | large_n | NULL | NULL | NULL | 989261 | Using where |
|
| 2 | SUBQUERY | small | const | small_u | small_u | 4 | const | 1 | |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+--------+-------------+
|
B22:
explain SELECT * FROM large WHERE n != (SELECT nn FROM small WHERE u = 6);
|
+------+-------------+-------+-------+---------------+---------+---------+-------+--------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+--------+-------------+
|
| 1 | PRIMARY | large | ALL | large_n | NULL | NULL | NULL | 989261 | Using where |
|
| 2 | SUBQUERY | small | const | small_u | small_u | 4 | const | 1 | |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+--------+-------------+
|
B23:
explain SELECT * FROM large WHERE nn != (SELECT n FROM small WHERE u = 6);
|
+------+-------------+-------+-------+---------------+---------+---------+-------+--------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+--------+-------------+
|
| 1 | PRIMARY | large | ALL | large_nn | NULL | NULL | NULL | 989261 | Using where |
|
| 2 | SUBQUERY | small | const | small_u | small_u | 4 | const | 1 | |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+--------+-------------+
|
B24:
explain SELECT * FROM large WHERE nn != (SELECT nn FROM small WHERE u = 6);
|
+------+-------------+-------+-------+---------------+---------+---------+-------+--------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+--------+-------------+
|
| 1 | PRIMARY | large | ALL | large_nn | NULL | NULL | NULL | 989261 | Using where |
|
| 2 | SUBQUERY | small | const | small_u | small_u | 4 | const | 1 | |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+--------+-------------+
|
C10:
explain SELECT * FROM small WHERE n IN (SELECT n FROM large);
|
+------+-------------+-------+------+---------------+---------+---------+--------------+------+--------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+---------+---------+--------------+------+--------------------------------+
|
| 1 | PRIMARY | small | ALL | small_n | NULL | NULL | NULL | 10 | Using where |
|
| 1 | PRIMARY | large | ref | large_n | large_n | 5 | test.small.n | 1 | Using index; FirstMatch(small) |
|
+------+-------------+-------+------+---------------+---------+---------+--------------+------+--------------------------------+
|
C11:
explain SELECT * FROM small WHERE n IN (SELECT nn FROM large);
|
+------+-------------+-------+------+---------------+----------+---------+--------------+------+--------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+----------+---------+--------------+------+--------------------------------+
|
| 1 | PRIMARY | small | ALL | small_n | NULL | NULL | NULL | 10 | Using where |
|
| 1 | PRIMARY | large | ref | large_nn | large_nn | 4 | test.small.n | 1 | Using index; FirstMatch(small) |
|
+------+-------------+-------+------+---------------+----------+---------+--------------+------+--------------------------------+
|
C12:
explain SELECT * FROM small WHERE nn IN (SELECT n FROM large);
|
+------+-------------+-------+------+---------------+---------+---------+---------------+------+--------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+---------+---------+---------------+------+--------------------------------+
|
| 1 | PRIMARY | small | ALL | small_nn | NULL | NULL | NULL | 10 | |
|
| 1 | PRIMARY | large | ref | large_n | large_n | 5 | test.small.nn | 1 | Using index; FirstMatch(small) |
|
+------+-------------+-------+------+---------------+---------+---------+---------------+------+--------------------------------+
|
C13:
explain SELECT * FROM small WHERE nn IN (SELECT nn FROM large);
|
+------+-------------+-------+------+---------------+----------+---------+---------------+------+--------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+----------+---------+---------------+------+--------------------------------+
|
| 1 | PRIMARY | small | ALL | small_nn | NULL | NULL | NULL | 10 | |
|
| 1 | PRIMARY | large | ref | large_nn | large_nn | 4 | test.small.nn | 1 | Using index; FirstMatch(small) |
|
+------+-------------+-------+------+---------------+----------+---------+---------------+------+--------------------------------+
|
C14:
explain SELECT * FROM small WHERE EXISTS (SELECT * FROM large);
|
+------+-------------+-------+-------+---------------+----------+---------+------+--------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+----------+---------+------+--------+-------------+
|
| 1 | PRIMARY | small | ALL | NULL | NULL | NULL | NULL | 10 | |
|
| 2 | SUBQUERY | large | index | NULL | large_nu | 4 | NULL | 989261 | Using index |
|
+------+-------------+-------+-------+---------------+----------+---------+------+--------+-------------+
|
C20:
explain SELECT * FROM large WHERE n IN (SELECT n FROM small);
|
+------+-------------+-------+-------+---------------+---------+---------+--------------+------+-------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+--------------+------+-------------------------------------+
|
| 1 | PRIMARY | small | index | small_n | small_n | 5 | NULL | 10 | Using where; Using index; LooseScan |
|
| 1 | PRIMARY | large | ref | large_n | large_n | 5 | test.small.n | 1 | |
|
+------+-------------+-------+-------+---------------+---------+---------+--------------+------+-------------------------------------+
|
C21:
explain SELECT * FROM large WHERE n IN (SELECT nn FROM small);
|
+------+-------------+-------+-------+---------------+----------+---------+---------------+------+------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+----------+---------+---------------+------+------------------------+
|
| 1 | PRIMARY | small | index | small_nn | small_nn | 4 | NULL | 10 | Using index; LooseScan |
|
| 1 | PRIMARY | large | ref | large_n | large_n | 5 | test.small.nn | 1 | |
|
+------+-------------+-------+-------+---------------+----------+---------+---------------+------+------------------------+
|
C22:
explain SELECT * FROM large WHERE nn IN (SELECT n FROM small);
|
+------+-------------+-------+-------+---------------+----------+---------+--------------+------+-------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+----------+---------+--------------+------+-------------------------------------+
|
| 1 | PRIMARY | small | index | small_n | small_n | 5 | NULL | 10 | Using where; Using index; LooseScan |
|
| 1 | PRIMARY | large | ref | large_nn | large_nn | 4 | test.small.n | 1 | |
|
+------+-------------+-------+-------+---------------+----------+---------+--------------+------+-------------------------------------+
|
C23:
explain SELECT * FROM large WHERE nn IN (SELECT nn FROM small);
|
+------+-------------+-------+-------+---------------+----------+---------+---------------+------+------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+----------+---------+---------------+------+------------------------+
|
| 1 | PRIMARY | small | index | small_nn | small_nn | 4 | NULL | 10 | Using index; LooseScan |
|
| 1 | PRIMARY | large | ref | large_nn | large_nn | 4 | test.small.nn | 1 | |
|
+------+-------------+-------+-------+---------------+----------+---------+---------------+------+------------------------+
|
C24:
explain SELECT * FROM large WHERE EXISTS (SELECT * FROM small);
|
+------+-------------+-------+-------+---------------+----------+---------+------+--------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+----------+---------+------+--------+-------------+
|
| 1 | PRIMARY | large | ALL | NULL | NULL | NULL | NULL | 989261 | |
|
| 2 | SUBQUERY | small | index | NULL | small_nu | 4 | NULL | 10 | Using index |
|
+------+-------------+-------+-------+---------------+----------+---------+------+--------+-------------+
|
D10:
explain SELECT * FROM small WHERE n NOT IN (SELECT n FROM large);
|
+------+-------------+-------+----------------+---------------+---------+---------+------+------+------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+----------------+---------------+---------+---------+------+------+------------------------------------+
|
| 1 | PRIMARY | small | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
|
| 2 | SUBQUERY | large | index_subquery | large_n | large_n | 5 | func | 2 | Using index; Full scan on NULL key |
|
+------+-------------+-------+----------------+---------------+---------+---------+------+------+------------------------------------+
|
D11:
explain SELECT * FROM small WHERE n NOT IN (SELECT nn FROM large);
|
+------+--------------------+-------+----------------+---------------+----------+---------+------+------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+----------------+---------------+----------+---------+------+------+-------------------------------------------------+
|
| 1 | PRIMARY | small | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
|
| 2 | DEPENDENT SUBQUERY | large | index_subquery | large_nn | large_nn | 4 | func | 1 | Using index; Using where; Full scan on NULL key |
|
+------+--------------------+-------+----------------+---------------+----------+---------+------+------+-------------------------------------------------+
|
D12:
explain SELECT * FROM small WHERE nn NOT IN (SELECT n FROM large);
|
+------+-------------+-------+----------------+---------------+---------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+----------------+---------------+---------+---------+------+------+-------------+
|
| 1 | PRIMARY | small | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
|
| 2 | SUBQUERY | large | index_subquery | large_n | large_n | 5 | func | 2 | Using index |
|
+------+-------------+-------+----------------+---------------+---------+---------+------+------+-------------+
|
D13:
explain SELECT * FROM small WHERE nn NOT IN (SELECT nn FROM large);
|
+------+--------------------+-------+----------------+---------------+----------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+----------------+---------------+----------+---------+------+------+-------------+
|
| 1 | PRIMARY | small | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
|
| 2 | DEPENDENT SUBQUERY | large | index_subquery | large_nn | large_nn | 4 | func | 1 | Using index |
|
+------+--------------------+-------+----------------+---------------+----------+---------+------+------+-------------+
|
D14:
explain SELECT * FROM small WHERE NOT EXISTS (SELECT * FROM large);
|
+------+-------------+-------+-------+---------------+----------+---------+------+--------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+----------+---------+------+--------+-------------+
|
| 1 | PRIMARY | small | ALL | NULL | NULL | NULL | NULL | 10 | |
|
| 2 | SUBQUERY | large | index | NULL | large_nu | 4 | NULL | 989261 | Using index |
|
+------+-------------+-------+-------+---------------+----------+---------+------+--------+-------------+
|
D20:
explain SELECT * FROM large WHERE n NOT IN (SELECT n FROM small);
|
+------+--------------+-------+-------+---------------+---------+---------+------+--------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------+-------+-------+---------------+---------+---------+------+--------+-------------+
|
| 1 | PRIMARY | large | ALL | NULL | NULL | NULL | NULL | 989261 | Using where |
|
| 2 | MATERIALIZED | small | index | small_n | small_n | 5 | NULL | 10 | Using index |
|
+------+--------------+-------+-------+---------------+---------+---------+------+--------+-------------+
|
D21:
explain SELECT * FROM large WHERE n NOT IN (SELECT nn FROM small);
|
+------+--------------+-------+-------+---------------+----------+---------+------+--------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------+-------+-------+---------------+----------+---------+------+--------+-------------+
|
| 1 | PRIMARY | large | ALL | NULL | NULL | NULL | NULL | 989261 | Using where |
|
| 2 | MATERIALIZED | small | index | small_nn | small_nn | 4 | NULL | 10 | Using index |
|
+------+--------------+-------+-------+---------------+----------+---------+------+--------+-------------+
|
D22:
explain SELECT * FROM large WHERE nn NOT IN (SELECT n FROM small);
|
+------+--------------+-------+-------+---------------+---------+---------+------+--------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------+-------+-------+---------------+---------+---------+------+--------+-------------+
|
| 1 | PRIMARY | large | ALL | NULL | NULL | NULL | NULL | 989261 | Using where |
|
| 2 | MATERIALIZED | small | index | small_n | small_n | 5 | NULL | 10 | Using index |
|
+------+--------------+-------+-------+---------------+---------+---------+------+--------+-------------+
|
D23:
explain SELECT * FROM large WHERE nn NOT IN (SELECT nn FROM small);
|
+------+--------------+-------+-------+---------------+----------+---------+------+--------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------+-------+-------+---------------+----------+---------+------+--------+-------------+
|
| 1 | PRIMARY | large | ALL | NULL | NULL | NULL | NULL | 989261 | Using where |
|
| 2 | MATERIALIZED | small | index | small_nn | small_nn | 4 | NULL | 10 | Using index |
|
+------+--------------+-------+-------+---------------+----------+---------+------+--------+-------------+
|
D24:
explain SELECT * FROM large WHERE NOT EXISTS (SELECT * FROM small);
|
+------+-------------+-------+-------+---------------+----------+---------+------+------+------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+----------+---------+------+------+------------------+
|
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
|
| 2 | SUBQUERY | small | index | NULL | small_nu | 4 | NULL | 10 | Using index |
|
+------+-------------+-------+-------+---------------+----------+---------+------+------+------------------+
|
E10:
explain SELECT * FROM small WHERE n IN (SELECT n FROM large WHERE large.u = small.u);
|
+------+-------------+-------+--------+-----------------+---------+---------+--------------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------+-----------------+---------+---------+--------------+------+-------------+
|
| 1 | PRIMARY | small | ALL | small_u,small_n | NULL | NULL | NULL | 10 | |
|
| 1 | PRIMARY | large | eq_ref | large_u,large_n | large_u | 4 | test.small.u | 1 | Using where |
|
+------+-------------+-------+--------+-----------------+---------+---------+--------------+------+-------------+
|
E11:
explain SELECT * FROM small WHERE n IN (SELECT nn FROM large WHERE large.u = small.u);
|
+------+-------------+-------+--------+------------------+---------+---------+--------------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------+------------------+---------+---------+--------------+------+-------------+
|
| 1 | PRIMARY | small | ALL | small_u,small_n | NULL | NULL | NULL | 10 | |
|
| 1 | PRIMARY | large | eq_ref | large_u,large_nn | large_u | 4 | test.small.u | 1 | Using where |
|
+------+-------------+-------+--------+------------------+---------+---------+--------------+------+-------------+
|
E12:
explain SELECT * FROM small WHERE nn IN (SELECT n FROM large WHERE large.u = small.u);
|
+------+-------------+-------+--------+------------------+---------+---------+--------------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------+------------------+---------+---------+--------------+------+-------------+
|
| 1 | PRIMARY | small | ALL | small_u,small_nn | NULL | NULL | NULL | 10 | |
|
| 1 | PRIMARY | large | eq_ref | large_u,large_n | large_u | 4 | test.small.u | 1 | Using where |
|
+------+-------------+-------+--------+------------------+---------+---------+--------------+------+-------------+
|
E13:
explain SELECT * FROM small WHERE nn IN (SELECT nn FROM large WHERE large.u = small.u);
|
+------+-------------+-------+--------+------------------+---------+---------+--------------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------+------------------+---------+---------+--------------+------+-------------+
|
| 1 | PRIMARY | small | ALL | small_u,small_nn | NULL | NULL | NULL | 10 | |
|
| 1 | PRIMARY | large | eq_ref | large_u,large_nn | large_u | 4 | test.small.u | 1 | Using where |
|
+------+-------------+-------+--------+------------------+---------+---------+--------------+------+-------------+
|
E14:
explain SELECT * FROM small WHERE n IN (SELECT n FROM large WHERE large.nu = small.u);
|
+------+-------------+-------+------+------------------+----------+---------+--------------+------+--------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+------------------+----------+---------+--------------+------+--------------------------------+
|
| 1 | PRIMARY | small | ALL | small_u,small_n | NULL | NULL | NULL | 10 | |
|
| 1 | PRIMARY | large | ref | large_nu,large_n | large_nu | 4 | test.small.u | 1 | Using where; FirstMatch(small) |
|
+------+-------------+-------+------+------------------+----------+---------+--------------+------+--------------------------------+
|
E15:
explain SELECT * FROM small WHERE n IN (SELECT nn FROM large WHERE large.nu = small.u);
|
+------+-------------+-------+------+-------------------+----------+---------+--------------+------+--------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+-------------------+----------+---------+--------------+------+--------------------------------+
|
| 1 | PRIMARY | small | ALL | small_u,small_n | NULL | NULL | NULL | 10 | |
|
| 1 | PRIMARY | large | ref | large_nu,large_nn | large_nu | 4 | test.small.u | 1 | Using where; FirstMatch(small) |
|
+------+-------------+-------+------+-------------------+----------+---------+--------------+------+--------------------------------+
|
E16:
explain SELECT * FROM small WHERE nn IN (SELECT n FROM large WHERE large.nu = small.u);
|
+------+-------------+-------+------+------------------+----------+---------+--------------+------+--------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+------------------+----------+---------+--------------+------+--------------------------------+
|
| 1 | PRIMARY | small | ALL | small_u,small_nn | NULL | NULL | NULL | 10 | |
|
| 1 | PRIMARY | large | ref | large_nu,large_n | large_nu | 4 | test.small.u | 1 | Using where; FirstMatch(small) |
|
+------+-------------+-------+------+------------------+----------+---------+--------------+------+--------------------------------+
|
E17:
explain SELECT * FROM small WHERE nn IN (SELECT nn FROM large WHERE large.nu = small.u);
|
+------+-------------+-------+------+-------------------+----------+---------+--------------+------+--------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+-------------------+----------+---------+--------------+------+--------------------------------+
|
| 1 | PRIMARY | small | ALL | small_u,small_nn | NULL | NULL | NULL | 10 | |
|
| 1 | PRIMARY | large | ref | large_nu,large_nn | large_nu | 4 | test.small.u | 1 | Using where; FirstMatch(small) |
|
+------+-------------+-------+------+-------------------+----------+---------+--------------+------+--------------------------------+
|
E18:
explain SELECT * FROM small WHERE EXISTS (SELECT * FROM large WHERE large.u = small.u);
|
+------+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+
|
| 1 | PRIMARY | small | ALL | small_u | NULL | NULL | NULL | 10 | |
|
| 1 | PRIMARY | large | eq_ref | large_u | large_u | 4 | test.small.u | 1 | Using index |
|
+------+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+
|
E19:
explain SELECT * FROM small WHERE EXISTS (SELECT * FROM large WHERE large.nu = small.u);
|
+------+-------------+-------+------+---------------+----------+---------+--------------+------+--------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+----------+---------+--------------+------+--------------------------------+
|
| 1 | PRIMARY | small | ALL | small_u | NULL | NULL | NULL | 10 | |
|
| 1 | PRIMARY | large | ref | large_nu | large_nu | 4 | test.small.u | 1 | Using index; FirstMatch(small) |
|
+------+-------------+-------+------+---------------+----------+---------+--------------+------+--------------------------------+
|
E20:
explain SELECT * FROM large WHERE n IN (SELECT n FROM small WHERE small.u = large.u);
|
+------+-------------+-------+--------+-----------------+---------+---------+--------------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------+-----------------+---------+---------+--------------+------+-------------+
|
| 1 | PRIMARY | small | index | small_u,small_n | small_n | 5 | NULL | 10 | Using index |
|
| 1 | PRIMARY | large | eq_ref | large_u,large_n | large_u | 4 | test.small.u | 1 | Using where |
|
+------+-------------+-------+--------+-----------------+---------+---------+--------------+------+-------------+
|
E21:
explain SELECT * FROM large WHERE n IN (SELECT nn FROM small WHERE small.u = large.u);
|
+------+-------------+-------+--------+------------------+----------+---------+--------------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------+------------------+----------+---------+--------------+------+-------------+
|
| 1 | PRIMARY | small | index | small_u,small_nn | small_nn | 4 | NULL | 10 | Using index |
|
| 1 | PRIMARY | large | eq_ref | large_u,large_n | large_u | 4 | test.small.u | 1 | Using where |
|
+------+-------------+-------+--------+------------------+----------+---------+--------------+------+-------------+
|
E22:
explain SELECT * FROM large WHERE nn IN (SELECT n FROM small WHERE small.u = large.u);
|
+------+-------------+-------+--------+------------------+---------+---------+--------------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------+------------------+---------+---------+--------------+------+-------------+
|
| 1 | PRIMARY | small | index | small_u,small_n | small_n | 5 | NULL | 10 | Using index |
|
| 1 | PRIMARY | large | eq_ref | large_u,large_nn | large_u | 4 | test.small.u | 1 | Using where |
|
+------+-------------+-------+--------+------------------+---------+---------+--------------+------+-------------+
|
E23:
explain SELECT * FROM large WHERE nn IN (SELECT nn FROM small WHERE small.u = large.u);
|
+------+-------------+-------+--------+------------------+----------+---------+--------------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------+------------------+----------+---------+--------------+------+-------------+
|
| 1 | PRIMARY | small | index | small_u,small_nn | small_nn | 4 | NULL | 10 | Using index |
|
| 1 | PRIMARY | large | eq_ref | large_u,large_nn | large_u | 4 | test.small.u | 1 | Using where |
|
+------+-------------+-------+--------+------------------+----------+---------+--------------+------+-------------+
|
E24:
explain SELECT * FROM large WHERE n IN (SELECT n FROM small WHERE small.nu = large.u);
|
+------+-------------+-------+--------+------------------+---------+---------+---------------+------+----------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------+------------------+---------+---------+---------------+------+----------------------------+
|
| 1 | PRIMARY | small | ALL | small_nu,small_n | NULL | NULL | NULL | 10 | Start temporary |
|
| 1 | PRIMARY | large | eq_ref | large_u,large_n | large_u | 4 | test.small.nu | 1 | Using where; End temporary |
|
+------+-------------+-------+--------+------------------+---------+---------+---------------+------+----------------------------+
|
E25:
explain SELECT * FROM large WHERE n IN (SELECT nn FROM small WHERE small.nu = large.u);
|
+------+-------------+-------+--------+-------------------+---------+---------+---------------+------+----------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------+-------------------+---------+---------+---------------+------+----------------------------+
|
| 1 | PRIMARY | small | ALL | small_nu,small_nn | NULL | NULL | NULL | 10 | Start temporary |
|
| 1 | PRIMARY | large | eq_ref | large_u,large_n | large_u | 4 | test.small.nu | 1 | Using where; End temporary |
|
+------+-------------+-------+--------+-------------------+---------+---------+---------------+------+----------------------------+
|
E26:
explain SELECT * FROM large WHERE nn IN (SELECT n FROM small WHERE small.nu = large.u);
|
+------+-------------+-------+--------+------------------+---------+---------+---------------+------+----------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------+------------------+---------+---------+---------------+------+----------------------------+
|
| 1 | PRIMARY | small | ALL | small_nu,small_n | NULL | NULL | NULL | 10 | Start temporary |
|
| 1 | PRIMARY | large | eq_ref | large_u,large_nn | large_u | 4 | test.small.nu | 1 | Using where; End temporary |
|
+------+-------------+-------+--------+------------------+---------+---------+---------------+------+----------------------------+
|
E27:
explain SELECT * FROM large WHERE nn IN (SELECT nn FROM small WHERE small.nu = large.u);
|
+------+-------------+-------+--------+-------------------+---------+---------+---------------+------+----------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------+-------------------+---------+---------+---------------+------+----------------------------+
|
| 1 | PRIMARY | small | ALL | small_nu,small_nn | NULL | NULL | NULL | 10 | Start temporary |
|
| 1 | PRIMARY | large | eq_ref | large_u,large_nn | large_u | 4 | test.small.nu | 1 | Using where; End temporary |
|
+------+-------------+-------+--------+-------------------+---------+---------+---------------+------+----------------------------+
|
E28:
explain SELECT * FROM large WHERE EXISTS (SELECT * FROM small WHERE small.u = large.u);
|
+------+-------------+-------+--------+---------------+----------+---------+--------------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------+---------------+----------+---------+--------------+------+-------------+
|
| 1 | PRIMARY | small | index | small_u | small_nu | 4 | NULL | 10 | Using index |
|
| 1 | PRIMARY | large | eq_ref | large_u | large_u | 4 | test.small.u | 1 | |
|
+------+-------------+-------+--------+---------------+----------+---------+--------------+------+-------------+
|
E29:
explain SELECT * FROM large WHERE EXISTS (SELECT * FROM small WHERE small.nu = large.u);
|
+------+-------------+-------+--------+---------------+----------+---------+---------------+------+------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------+---------------+----------+---------+---------------+------+------------------------+
|
| 1 | PRIMARY | small | index | small_nu | small_nu | 4 | NULL | 10 | Using index; LooseScan |
|
| 1 | PRIMARY | large | eq_ref | large_u | large_u | 4 | test.small.nu | 1 | |
|
+------+-------------+-------+--------+---------------+----------+---------+---------------+------+------------------------+
|
F10:
explain SELECT * FROM small WHERE n NOT IN (SELECT n FROM large WHERE large.u = small.u);
|
+------+--------------------+-------+--------+-----------------------------------+---------+---------+--------------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+--------+-----------------------------------+---------+---------+--------------+------+-------------+
|
| 1 | PRIMARY | small | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
|
| 2 | DEPENDENT SUBQUERY | large | eq_ref | large_u,large_nu,large_n,large_nn | large_u | 4 | test.small.u | 1 | Using where |
|
+------+--------------------+-------+--------+-----------------------------------+---------+---------+--------------+------+-------------+
|
F11:
explain SELECT * FROM small WHERE n NOT IN (SELECT nn FROM large WHERE large.u = small.u);
|
+------+--------------------+-------+--------+-----------------------------------+---------+---------+--------------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+--------+-----------------------------------+---------+---------+--------------+------+-------------+
|
| 1 | PRIMARY | small | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
|
| 2 | DEPENDENT SUBQUERY | large | eq_ref | large_u,large_nu,large_n,large_nn | large_u | 4 | test.small.u | 1 | Using where |
|
+------+--------------------+-------+--------+-----------------------------------+---------+---------+--------------+------+-------------+
|
F12:
explain SELECT * FROM small WHERE nn NOT IN (SELECT n FROM large WHERE large.u = small.u);
|
+------+--------------------+-------+--------+-----------------------------------+---------+---------+--------------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+--------+-----------------------------------+---------+---------+--------------+------+-------------+
|
| 1 | PRIMARY | small | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
|
| 2 | DEPENDENT SUBQUERY | large | eq_ref | large_u,large_nu,large_n,large_nn | large_u | 4 | test.small.u | 1 | Using where |
|
+------+--------------------+-------+--------+-----------------------------------+---------+---------+--------------+------+-------------+
|
F13:
explain SELECT * FROM small WHERE nn NOT IN (SELECT nn FROM large WHERE large.u = small.u);
|
+------+--------------------+-------+--------+-----------------------------------+---------+---------+--------------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+--------+-----------------------------------+---------+---------+--------------+------+-------------+
|
| 1 | PRIMARY | small | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
|
| 2 | DEPENDENT SUBQUERY | large | eq_ref | large_u,large_nu,large_n,large_nn | large_u | 4 | test.small.u | 1 | Using where |
|
+------+--------------------+-------+--------+-----------------------------------+---------+---------+--------------+------+-------------+
|
F14:
explain SELECT * FROM small WHERE n NOT IN (SELECT n FROM large WHERE large.nu = small.u);
|
+------+--------------------+-------+------+------------------+----------+---------+--------------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+------+------------------+----------+---------+--------------+------+-------------+
|
| 1 | PRIMARY | small | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
|
| 2 | DEPENDENT SUBQUERY | large | ref | large_nu,large_n | large_nu | 4 | test.small.u | 1 | Using where |
|
+------+--------------------+-------+------+------------------+----------+---------+--------------+------+-------------+
|
F15:
explain SELECT * FROM small WHERE n NOT IN (SELECT nn FROM large WHERE large.nu = small.u);
|
+------+--------------------+-------+------+-------------------+----------+---------+--------------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+------+-------------------+----------+---------+--------------+------+-------------+
|
| 1 | PRIMARY | small | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
|
| 2 | DEPENDENT SUBQUERY | large | ref | large_nu,large_nn | large_nu | 4 | test.small.u | 1 | Using where |
|
+------+--------------------+-------+------+-------------------+----------+---------+--------------+------+-------------+
|
F16:
explain SELECT * FROM small WHERE nn NOT IN (SELECT n FROM large WHERE large.nu = small.u);
|
+------+--------------------+-------+------+------------------+----------+---------+--------------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+------+------------------+----------+---------+--------------+------+-------------+
|
| 1 | PRIMARY | small | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
|
| 2 | DEPENDENT SUBQUERY | large | ref | large_nu,large_n | large_nu | 4 | test.small.u | 1 | Using where |
|
+------+--------------------+-------+------+------------------+----------+---------+--------------+------+-------------+
|
F17:
explain SELECT * FROM small WHERE nn NOT IN (SELECT nn FROM large WHERE large.nu = small.u);
|
+------+--------------------+-------+------+-------------------+----------+---------+--------------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+------+-------------------+----------+---------+--------------+------+-------------+
|
| 1 | PRIMARY | small | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
|
| 2 | DEPENDENT SUBQUERY | large | ref | large_nu,large_nn | large_nu | 4 | test.small.u | 1 | Using where |
|
+------+--------------------+-------+------+-------------------+----------+---------+--------------+------+-------------+
|
F18:
explain SELECT * FROM small WHERE NOT EXISTS (SELECT * FROM large WHERE large.u = small.u);
|
+------+--------------------+-------+-----------------+---------------+---------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+-----------------+---------------+---------+---------+------+------+-------------+
|
| 1 | PRIMARY | small | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
|
| 2 | DEPENDENT SUBQUERY | large | unique_subquery | large_u | large_u | 4 | func | 1 | Using index |
|
+------+--------------------+-------+-----------------+---------------+---------+---------+------+------+-------------+
|
F19:
explain SELECT * FROM small WHERE NOT EXISTS (SELECT * FROM large WHERE large.nu = small.u);
|
+------+--------------------+-------+----------------+---------------+----------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+----------------+---------------+----------+---------+------+------+-------------+
|
| 1 | PRIMARY | small | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
|
| 2 | DEPENDENT SUBQUERY | large | index_subquery | large_nu | large_nu | 4 | func | 1 | Using index |
|
+------+--------------------+-------+----------------+---------------+----------+---------+------+------+-------------+
|
F20:
explain SELECT * FROM large WHERE n NOT IN (SELECT n FROM small WHERE small.u = large.u);
|
+------+--------------------+-------+--------+-----------------------------------+---------+---------+--------------+--------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+--------+-----------------------------------+---------+---------+--------------+--------+-------------+
|
| 1 | PRIMARY | large | ALL | NULL | NULL | NULL | NULL | 989261 | Using where |
|
| 2 | DEPENDENT SUBQUERY | small | eq_ref | small_u,small_nu,small_n,small_nn | small_u | 4 | test.large.u | 1 | Using where |
|
+------+--------------------+-------+--------+-----------------------------------+---------+---------+--------------+--------+-------------+
|
F21:
explain SELECT * FROM large WHERE n NOT IN (SELECT nn FROM small WHERE small.u = large.u);
|
+------+--------------------+-------+--------+-----------------------------------+---------+---------+--------------+--------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+--------+-----------------------------------+---------+---------+--------------+--------+-------------+
|
| 1 | PRIMARY | large | ALL | NULL | NULL | NULL | NULL | 989261 | Using where |
|
| 2 | DEPENDENT SUBQUERY | small | eq_ref | small_u,small_nu,small_n,small_nn | small_u | 4 | test.large.u | 1 | Using where |
|
+------+--------------------+-------+--------+-----------------------------------+---------+---------+--------------+--------+-------------+
|
F22:
explain SELECT * FROM large WHERE nn NOT IN (SELECT n FROM small WHERE small.u = large.u);
|
+------+--------------------+-------+--------+-----------------------------------+---------+---------+--------------+--------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+--------+-----------------------------------+---------+---------+--------------+--------+-------------+
|
| 1 | PRIMARY | large | ALL | NULL | NULL | NULL | NULL | 989261 | Using where |
|
| 2 | DEPENDENT SUBQUERY | small | eq_ref | small_u,small_nu,small_n,small_nn | small_u | 4 | test.large.u | 1 | Using where |
|
+------+--------------------+-------+--------+-----------------------------------+---------+---------+--------------+--------+-------------+
|
F23:
explain SELECT * FROM large WHERE nn NOT IN (SELECT nn FROM small WHERE small.u = large.u);
|
+------+--------------------+-------+--------+-----------------------------------+---------+---------+--------------+--------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+--------+-----------------------------------+---------+---------+--------------+--------+-------------+
|
| 1 | PRIMARY | large | ALL | NULL | NULL | NULL | NULL | 989261 | Using where |
|
| 2 | DEPENDENT SUBQUERY | small | eq_ref | small_u,small_nu,small_n,small_nn | small_u | 4 | test.large.u | 1 | Using where |
|
+------+--------------------+-------+--------+-----------------------------------+---------+---------+--------------+--------+-------------+
|
F24:
explain SELECT * FROM large WHERE n NOT IN (SELECT n FROM small WHERE small.nu = large.u);
|
+------+--------------------+-------+------+------------------+----------+---------+--------------+--------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+------+------------------+----------+---------+--------------+--------+-------------+
|
| 1 | PRIMARY | large | ALL | NULL | NULL | NULL | NULL | 989261 | Using where |
|
| 2 | DEPENDENT SUBQUERY | small | ref | small_nu,small_n | small_nu | 4 | test.large.u | 1 | Using where |
|
+------+--------------------+-------+------+------------------+----------+---------+--------------+--------+-------------+
|
F25:
explain SELECT * FROM large WHERE n NOT IN (SELECT nn FROM small WHERE small.nu = large.u);
|
+------+--------------------+-------+------+-------------------+----------+---------+--------------+--------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+------+-------------------+----------+---------+--------------+--------+-------------+
|
| 1 | PRIMARY | large | ALL | NULL | NULL | NULL | NULL | 989261 | Using where |
|
| 2 | DEPENDENT SUBQUERY | small | ref | small_nu,small_nn | small_nu | 4 | test.large.u | 1 | Using where |
|
+------+--------------------+-------+------+-------------------+----------+---------+--------------+--------+-------------+
|
F26:
explain SELECT * FROM large WHERE nn NOT IN (SELECT n FROM small WHERE small.nu = large.u);
|
+------+--------------------+-------+------+------------------+----------+---------+--------------+--------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+------+------------------+----------+---------+--------------+--------+-------------+
|
| 1 | PRIMARY | large | ALL | NULL | NULL | NULL | NULL | 989261 | Using where |
|
| 2 | DEPENDENT SUBQUERY | small | ref | small_nu,small_n | small_nu | 4 | test.large.u | 1 | Using where |
|
+------+--------------------+-------+------+------------------+----------+---------+--------------+--------+-------------+
|
F27:
explain SELECT * FROM large WHERE nn NOT IN (SELECT nn FROM small WHERE small.nu = large.u);
|
+------+--------------------+-------+------+-------------------+----------+---------+--------------+--------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+------+-------------------+----------+---------+--------------+--------+-------------+
|
| 1 | PRIMARY | large | ALL | NULL | NULL | NULL | NULL | 989261 | Using where |
|
| 2 | DEPENDENT SUBQUERY | small | ref | small_nu,small_nn | small_nu | 4 | test.large.u | 1 | Using where |
|
+------+--------------------+-------+------+-------------------+----------+---------+--------------+--------+-------------+
|
F28:
explain SELECT * FROM large WHERE NOT EXISTS (SELECT * FROM small WHERE small.u = large.u);
|
+------+--------------+-------+-------+---------------+----------+---------+------+--------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------+-------+-------+---------------+----------+---------+------+--------+-------------+
|
| 1 | PRIMARY | large | ALL | NULL | NULL | NULL | NULL | 989261 | Using where |
|
| 2 | MATERIALIZED | small | index | small_u | small_nu | 4 | NULL | 10 | Using index |
|
+------+--------------+-------+-------+---------------+----------+---------+------+--------+-------------+
|
F29:
explain SELECT * FROM large WHERE NOT EXISTS (SELECT * FROM small WHERE small.nu = large.u);
|
+------+--------------+-------+-------+---------------+----------+---------+------+--------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------+-------+-------+---------------+----------+---------+------+--------+-------------+
|
| 1 | PRIMARY | large | ALL | NULL | NULL | NULL | NULL | 989261 | Using where |
|
| 2 | MATERIALIZED | small | index | small_nu | small_nu | 4 | NULL | 10 | Using index |
|
+------+--------------+-------+-------+---------------+----------+---------+------+--------+-------------+
|