Details
Major Description
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)
|
Attachments
Issue Links
- is part of
-
MDEV-15253 Default optimizer setting changes for MariaDB 10.4
-
- Closed
-
* thread #2, stop reason = step overframe #0: 0x0000000100554e42 mysqld`best_access_path(join=0x0000625000024378, s=0x0000625000025fd0, remaining_tables=0, idx=0, disable_jbuf=<unavailable>, record_count=1, pos=0x00006250000264f8, loose_scan_pos=0x0000000000000004) at sql_select.cc:62786275 account here for range/index_merge access. Find out why this is so.6276 */6277 tmp= record_count *-> 6278 (s->quick->read_time +6279 (s->found_records - rnd_records)/(double) TIME_FOR_COMPARE);62806281 loose_scan_opt.check_range_access(join, idx, s->quick);Target 0: (mysqld) stopped.(lldb) p s->found_records(ha_rows) $4 = 8(lldb) p rnd_records(double) $5 = 15Here s->found_records has estimates from the GROUP BY optimization(loosescan)
while the rnd_records which we get through selectivity analysis does not take into account GROUP BY optimization