|
MariaDB [test]> CREATE TABLE t1 (a INT, b INT, INDEX (a,b));
|
Query OK, 0 rows affected (0.02 sec)
|
|
MariaDB [test]> INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5),
|
(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);
|
Query OK, 15 rows affected (0.01 sec)
|
Records: 15 Duplicates: 0 Warnings: 0
|
Case 1:
MariaDB [test]> set @@optimizer_use_condition_selectivity=4;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> set @@use_stat_tables= PREFERABLY;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> explain SELECT 1 FROM t1 AS t1_outer WHERE
|
-> a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
|
+------+--------------+-------------+--------+---------------+--------------+---------+-----------------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------+-------------+--------+---------------+--------------+---------+-----------------+------+--------------------------+
|
| 1 | PRIMARY | t1_outer | index | a | a | 10 | NULL | 16 | Using where; Using index |
|
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 4 | test.t1_outer.a | 1 | |
|
| 2 | MATERIALIZED | t1 | range | NULL | a | 5 | NULL | 5 | Using index for group-by |
|
+------+--------------+-------------+--------+---------------+--------------+---------+-----------------+------+--------------------------+
|
3 rows in set (0.01 sec)
|
Case 2:
MariaDB [test]> set @@optimizer_use_condition_selectivity=1;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> set @@use_stat_tables= NEVER;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> explain SELECT 1 FROM t1 AS t1_outer WHERE
|
-> a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
|
+------+--------------+-------------+-------+---------------+------+---------+--------------------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------+-------------+-------+---------------+------+---------+--------------------+------+--------------------------+
|
| 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 4 | |
|
| 1 | PRIMARY | t1_outer | ref | a | a | 5 | <subquery2>.max(b) | 4 | Using index |
|
| 2 | MATERIALIZED | t1 | range | NULL | a | 5 | NULL | 4 | Using index for group-by |
|
+------+--------------+-------------+-------+---------------+------+---------+--------------------+------+--------------------------+
|
3 rows in set (0.00 sec)
|
|