[MDEV-18835] Try queries from "How well a Query Optimizer Handles Subqueries" on MariaDB Created: 2019-03-06  Updated: 2019-03-06  Resolved: 2019-03-06

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Fix Version/s: N/A

Type: Task Priority: Major
Reporter: Sergei Petrunia Assignee: Sergei Petrunia
Resolution: Done Votes: 0
Labels: benchmarking, optimizer, subquery

Attachments: File mdev18835-fill-dataset.sql    

 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 |
 
+------+--------------+-------+-------+---------------+----------+---------+------+--------+-------------+


Generated at Thu Feb 08 08:47:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.