Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.5, 10.6, 10.11
-
None
Description
Queries.sql
has all the queries rrequired to reproduce issue.
Please see the explain plans for various versions below
|
CS 10.4.32. Uses correct index regardless of the value passed in the where clause |
MariaDB [test]> ANALYZE SELECT a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_01' GROUP BY a.c3; |
+------+-------------+-------+------+---------------+--------+---------+-------+------+---------+----------+------------+---------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+-------+------+---------------+--------+---------+-------+------+---------+----------+------------+---------------------------------------------------------------------+
|
| 1 | SIMPLE | a | ref | indx01 | indx01 | 52 | const | 3000 | 3000.00 | 100.00 | 100.00 | Using index condition; Using where; Using temporary; Using filesort | |
+------+-------------+-------+------+---------------+--------+---------+-------+------+---------+----------+------------+---------------------------------------------------------------------+
|
1 row in set (0.013 sec) |
 |
MariaDB [test]>
|
MariaDB [test]> ANALYZE SELECT a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_06' GROUP BY a.c3; |
+------+-------------+-------+------+---------------+--------+---------+-------+------+--------+----------+------------+---------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+-------+------+---------------+--------+---------+-------+------+--------+----------+------------+---------------------------------------------------------------------+
|
| 1 | SIMPLE | a | ref | indx01 | indx01 | 52 | const | 1 | 1.00 | 100.00 | 100.00 | Using index condition; Using where; Using temporary; Using filesort | |
+------+-------------+-------+------+---------------+--------+---------+-------+------+--------+----------+------------+---------------------------------------------------------------------+
|
1 row in set (0.005 sec) |
|
CS 10.5.23. Uses incorrect index when value passed doesn't have good selectivity |
MariaDB [test]> ANALYZE SELECT a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_01' GROUP BY a.c3; |
+------+-------------+-------+-------+---------------+--------+---------+------+-------+----------+----------+------------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+-------+-------+---------------+--------+---------+------+-------+----------+----------+------------+-------------+
|
| 1 | SIMPLE | a | index | indx01 | indx02 | 4 | NULL | 14697 | 15001.00 | 20.41 | 20.00 | Using where | |
+------+-------------+-------+-------+---------------+--------+---------+------+-------+----------+----------+------------+-------------+
|
1 row in set (0.083 sec) |
 |
MariaDB [test]>
|
MariaDB [test]> ANALYZE SELECT a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_06' GROUP BY a.c3; |
+------+-------------+-------+------+---------------+--------+---------+-------+------+--------+----------+------------+---------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+-------+------+---------------+--------+---------+-------+------+--------+----------+------------+---------------------------------------------------------------------+
|
| 1 | SIMPLE | a | ref | indx01 | indx01 | 52 | const | 1 | 1.00 | 100.00 | 100.00 | Using index condition; Using where; Using temporary; Using filesort | |
+------+-------------+-------+------+---------------+--------+---------+-------+------+--------+----------+------------+---------------------------------------------------------------------+
|
1 row in set (0.003 sec) |
|
CS 10.6.16. Same behaviour as 10.5.23 |
MariaDB [test]> ANALYZE SELECT a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_01' GROUP BY a.c3; |
+------+-------------+-------+-------+---------------+--------+---------+------+-------+----------+----------+------------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+-------+-------+---------------+--------+---------+------+-------+----------+----------+------------+-------------+
|
| 1 | SIMPLE | a | index | indx01 | indx02 | 4 | NULL | 14700 | 15001.00 | 20.41 | 20.00 | Using where | |
+------+-------------+-------+-------+---------------+--------+---------+------+-------+----------+----------+------------+-------------+
|
1 row in set (0.098 sec) |
 |
MariaDB [test]>
|
MariaDB [test]> ANALYZE SELECT a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_06' GROUP BY a.c3; |
+------+-------------+-------+------+---------------+--------+---------+-------+------+--------+----------+------------+---------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+-------+------+---------------+--------+---------+-------+------+--------+----------+------------+---------------------------------------------------------------------+
|
| 1 | SIMPLE | a | ref | indx01 | indx01 | 52 | const | 1 | 1.00 | 100.00 | 100.00 | Using index condition; Using where; Using temporary; Using filesort | |
+------+-------------+-------+------+---------------+--------+---------+-------+------+--------+----------+------------+---------------------------------------------------------------------+
|
1 row in set (0.005 sec) |
|
Ver 10.6.16. using FORCE INDEX shows how it would scan less rows for the same query if it uses the 'correct' index. The same index that it automatically selects in 10.4 |
MariaDB [test]> ANALYZE SELECT a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a FORCE INDEX (indx02) WHERE a.c2='c2_01' GROUP BY a.c3;
|
+------+-------------+-------+-------+---------------+--------+---------+------+-------+----------+----------+------------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+-------+-------+---------------+--------+---------+------+-------+----------+----------+------------+-------------+
|
| 1 | SIMPLE | a | index | NULL | indx02 | 4 | NULL | 14700 | 15001.00 | 100.00 | 20.00 | Using where |
|
+------+-------------+-------+-------+---------------+--------+---------+------+-------+----------+----------+------------+-------------+
|