Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-18835

Try queries from "How well a Query Optimizer Handles Subqueries" on MariaDB

    XMLWordPrintable

    Details

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

        Attachments

          Activity

            People

            Assignee:
            psergey Sergei Petrunia
            Reporter:
            psergey Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: