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 |
|
+------+-------------+-------+-------+---------------+--------+---------+------+-------+----------+----------+------------+-------------+
|
I used a test from
MDEV-33306.sql to get rid of rand() and test on the same dataset on different versions. 10.5-10.11 are slower than 10.4, while 11.0+ has around the same query execution time as 10.410.4
MariaDB [test]> analyze format=json SELECT sql_no_cache a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_01' GROUP BY a.c3;
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 114.24,
"filesort": {
"sort_key": "a.c3",
"r_loops": 1,
"r_total_time_ms": 0.1463,
"r_used_priority_queue": false,
"r_output_rows": 201,
"r_buffer_size": "4Kb",
"temporary_table": {
"table": {
"table_name": "a",
"access_type": "ref",
"possible_keys": ["indx01"],
"key": "indx01",
"key_length": "52",
"used_key_parts": ["c2"],
"ref": ["const"],
"r_loops": 1,
"rows": 3000,
"r_rows": 3000,
"r_total_time_ms": 104.39,
"filtered": 100,
"r_filtered": 100,
"index_condition": "a.c2 = 'c2_01'",
"attached_condition": "a.c2 <=> 'c2_01'"
}
}
}
}
}
10.5( and ~the same on 10.6-10.11)
MariaDB [test]> analyze format=json SELECT sql_no_cache a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_01' GROUP BY a.c3;
| {
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 668.6833897,
"table": {
"table_name": "a",
"access_type": "index",
"possible_keys": ["indx01"],
"key": "indx02",
"key_length": "4",
"used_key_parts": ["c3"],
"r_loops": 1,
"rows": 14697,
"r_rows": 15000,
"r_table_time_ms": 654.9452669,
"r_other_time_ms": 13.68485618,
"filtered": 20.41232872,
"r_filtered": 20,
"attached_condition": "a.c2 = 'c2_01'"
}
}
} |
11.0(-~11.4)
MariaDB [test]> analyze format=json SELECT sql_no_cache a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_01' GROUP BY a.c3;
{
"query_optimization": {
"r_total_time_ms": 2.986958457
},
"query_block": {
"select_id": 1,
"cost": 4.465185951,
"r_loops": 1,
"r_total_time_ms": 103.0670346,
"filesort": {
"sort_key": "a.c3",
"r_loops": 1,
"r_total_time_ms": 0.163121247,
"r_used_priority_queue": false,
"r_output_rows": 201,
"r_buffer_size": "4Kb",
"r_sort_mode": "sort_key,rowid",
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "a",
"access_type": "ALL",
"possible_keys": ["indx01"],
"loops": 1,
"r_loops": 1,
"rows": 14700,
"r_rows": 15000,
"cost": 2.4890156,
"r_table_time_ms": 84.82377979,
"r_other_time_ms": 17.77049086,
"r_engine_stats": {
"pages_accessed": 59
},
"filtered": 20.40816307,
"r_filtered": 20,
"attached_condition": "a.c2 = 'c2_01'"
}
}
]
}
}
}
}
10.4:
s| SELECT sql_no_cache a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_01' GROUP BY a.c3 | {
"steps": [
{
"join_preparation": {
"select_id": 1,
"steps": [
{
"expanded_query": "select sql_no_cache a.c3 AS c3,sum(a.c7) AS `SUM(a.c7)`,count(a.c1) AS `COUNT(a.c1)` from tab1 a where a.c2 = 'c2_01' group by a.c3"
}
]
}
},
{
"join_optimization": {
"select_id": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "a.c2 = 'c2_01'",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "multiple equal('c2_01', a.c2)"
},
{
"transformation": "constant_propagation",
"resulting_condition": "multiple equal('c2_01', a.c2)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "multiple equal('c2_01', a.c2)"
}
]
}
},
{
"table_dependencies": [
{
"table": "a",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": []
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "a",
"field": "c2",
"equals": "'c2_01'",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"table": "a",
"range_analysis": {
"table_scan": {
"rows": 14697,
"cost": 3038.5
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not applicable"
},
{
"index": "indx01",
"usable": true,
"key_parts": ["c2", "c7", "c1"]
},
{
"index": "indx02",
"usable": true,
"key_parts": ["c3", "c1", "c2"]
},
{
"index": "indx03",
"usable": false,
"cause": "not applicable"
},
{
"index": "indx04",
"usable": false,
"cause": "not applicable"
}
],
"setup_range_conditions": [],
"group_index_range": {
"chosen": false,
"cause": "not applicable aggregate function"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "indx01",
"ranges": ["(c2_01) <= (c2) <= (c2_01)"],
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 3000,
"cost": 3783.4,
"chosen": false,
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"cause": "too few roworder scans"
},
"analyzing_index_merge_union": []
}
}
},
{
"selectivity_for_indexes": [
{
"index_name": "indx01",
"selectivity_from_index": 0.2041
}
],
"selectivity_for_columns": [],
"cond_selectivity": 0.2041
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [],
"table": "a",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "indx01",
"used_range_estimates": true,
"rowid_filter_skipped": "worst/max seeks clipping",
"rows": 3000,
"cost": 292,
"chosen": true
},
{
"access_type": "scan",
"resulting_rows": 3000,
"cost": 2436.4,
"chosen": false
}
],
"chosen_access_method": {
"type": "ref",
"records": 3000,
"cost": 292,
"uses_join_buffering": false
}
},
"rows_for_plan": 3000,
"cost_for_plan": 892,
"estimated_join_cardinality": 3000
}
]
},
{
"best_join_order": ["a"]
},
{
"attaching_conditions_to_tables": {
"original_condition": "a.c2 = 'c2_01'",
"attached_conditions_computation": [],
"attached_conditions_summary": [
{
"table": "a",
"attached": "a.c2 = 'c2_01'"
}
]
}
},
{
"test_if_skip_sort_order": [
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "GROUP BY",
"fanout": 1,
"read_time": 292,
"table": "a",
"rows_estimation": 3000,
"possible_keys": [
{
"index": "PRIMARY",
"can_resolve_order": false,
"cause": "not usable index for the query"
},
{
"index": "indx01",
"can_resolve_order": false,
"cause": "not usable index for the query"
},
{
"index": "indx02",
"can_resolve_order": true,
"usable": false
},
{
"index": "indx03",
"can_resolve_order": false,
"cause": "not usable index for the query"
},
{
"index": "indx04",
"can_resolve_order": false,
"cause": "not usable index for the query"
}
]
}
}
]
}
]
}
},
{
"join_execution": {
"select_id": 1,
"steps": []
}
}
]
}
10.5 (-10.11):
SELECT sql_no_cache a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_01' GROUP BY a.c3 | {
"steps": [
{
"join_preparation": {
"select_id": 1,
"steps": [
{
"expanded_query": "select sql_no_cache a.c3 AS c3,sum(a.c7) AS `SUM(a.c7)`,count(a.c1) AS `COUNT(a.c1)` from tab1 a where a.c2 = 'c2_01' group by a.c3"
}
]
}
},
{
"join_optimization": {
"select_id": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "a.c2 = 'c2_01'",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "multiple equal('c2_01', a.c2)"
},
{
"transformation": "constant_propagation",
"resulting_condition": "multiple equal('c2_01', a.c2)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "multiple equal('c2_01', a.c2)"
}
]
}
},
{
"table_dependencies": [
{
"table": "a",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": []
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "a",
"field": "c2",
"equals": "'c2_01'",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"table": "a",
"range_analysis": {
"table_scan": {
"rows": 14697,
"cost": 3038.4
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not applicable"
},
{
"index": "indx01",
"usable": true,
"key_parts": ["c2", "c7", "c1"]
},
{
"index": "indx02",
"usable": true,
"key_parts": ["c3", "c1", "c2"]
},
{
"index": "indx03",
"usable": false,
"cause": "not applicable"
},
{
"index": "indx04",
"usable": false,
"cause": "not applicable"
}
],
"setup_range_conditions": [],
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "indx01",
"ranges": ["(c2_01) <= (c2) <= (c2_01)"],
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 3000,
"cost": 3631.392788,
"chosen": false,
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"cause": "too few roworder scans"
},
"analyzing_index_merge_union": []
},
"group_index_range": {
"chosen": false,
"cause": "not applicable aggregate function"
}
}
},
{
"selectivity_for_indexes": [
{
"index_name": "indx01",
"selectivity_from_index": 0.20412329
}
],
"selectivity_for_columns": [],
"cond_selectivity": 0.20412329
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [],
"table": "a",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "indx01",
"used_range_estimates": true,
"rowid_filter_skipped": "worst/max seeks clipping",
"rows": 3000,
"cost": 3031.372788,
"chosen": true
},
{
"access_type": "scan",
"resulting_rows": 3000,
"cost": 2436.4,
"chosen": true,
"use_tmp_table": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 3000,
"cost": 2436.4,
"uses_join_buffering": false
}
},
"rows_for_plan": 3000,
"cost_for_plan": 3036.4,
"cost_for_sorting": 3000,
"estimated_join_cardinality": 3000
}
]
},
{
"best_join_order": ["a"]
},
{
"attaching_conditions_to_tables": {
"original_condition": "a.c2 = 'c2_01'",
"attached_conditions_computation": [],
"attached_conditions_summary": [
{
"table": "a",
"attached": "a.c2 = 'c2_01'"
}
]
}
},
{
"test_if_skip_sort_order": [
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "GROUP BY",
"fanout": 1,
"read_time": 2436.401,
"table": "a",
"rows_estimation": 3000,
"possible_keys": [
{
"index": "PRIMARY",
"can_resolve_order": false,
"cause": "not usable index for the query"
},
{
"index": "indx01",
"can_resolve_order": false,
"cause": "not usable index for the query"
},
{
"index": "indx02",
"can_resolve_order": true,
"updated_limit": 14697,
"index_scan_time": 14697,
"records": 14697,
"chosen": true
},
{
"index": "indx03",
"can_resolve_order": false,
"cause": "not usable index for the query"
},
{
"index": "indx04",
"can_resolve_order": false,
"cause": "not usable index for the query"
}
]
}
}
]
}
]
}
},
{
"join_execution": {
"select_id": 1,
"steps": []
}
}
]
}
11.0 (-11.4)
SELECT sql_no_cache a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_01' GROUP BY a.c3 | {
"steps": [
{
"join_preparation": {
"select_id": 1,
"steps": [
{
"expanded_query": "select sql_no_cache a.c3 AS c3,sum(a.c7) AS `SUM(a.c7)`,count(a.c1) AS `COUNT(a.c1)` from tab1 a where a.c2 = 'c2_01' group by a.c3"
}
]
}
},
{
"join_optimization": {
"select_id": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "a.c2 = 'c2_01'",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "multiple equal('c2_01', a.c2)"
},
{
"transformation": "constant_propagation",
"resulting_condition": "multiple equal('c2_01', a.c2)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "multiple equal('c2_01', a.c2)"
}
]
}
},
{
"table_dependencies": [
{
"table": "a",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": []
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "a",
"index": "indx01",
"field": "c2",
"equals": "'c2_01'",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"table": "a",
"range_analysis": {
"table_scan": {
"rows": 14700,
"cost": 2.4890156
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not applicable"
},
{
"index": "indx01",
"usable": true,
"key_parts": ["c2", "c7", "c1"]
},
{
"index": "indx02",
"usable": true,
"key_parts": ["c3", "c1", "c2"]
},
{
"index": "indx03",
"usable": false,
"cause": "not applicable"
},
{
"index": "indx04",
"usable": false,
"cause": "not applicable"
}
],
"setup_range_conditions": [],
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "indx01",
"ranges": ["(c2_01) <= (c2) <= (c2_01)"],
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 3000,
"cost": 3.08444424,
"chosen": false,
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"cause": "too few roworder scans"
},
"analyzing_index_merge_union": []
},
"group_index_range": {
"chosen": false,
"cause": "not applicable aggregate function"
}
}
},
{
"selectivity_for_indexes": [
{
"index_name": "indx01",
"selectivity_from_index": 0.204081633
}
],
"selectivity_for_columns": [],
"cond_selectivity": 0.204081633
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": "",
"get_costs_for_tables": [
{
"best_access_path": {
"table": "a",
"plan_details": {
"record_count": 1
},
"considered_access_paths": [
{
"access_type": "ref",
"index": "indx01",
"used_range_estimates": true,
"rows": 3000,
"cost": 3.08365312,
"chosen": true
},
{
"access_type": "scan",
"rows": 14700,
"rows_after_filter": 3000,
"rows_out": 3000,
"cost": 2.4890156,
"index_only": false,
"chosen": true,
"use_tmp_table": true
}
],
"chosen_access_method": {
"type": "scan",
"rows_read": 3000,
"rows_out": 3000,
"cost": 2.4890156,
"uses_join_buffering": false
}
}
}
]
},
{
"plan_prefix": "",
"table": "a",
"rows_for_plan": 3000,
"cost_for_plan": 2.4890156,
"cost_for_sorting": 1.976170351
}
]
},
{
"best_join_order": ["a"],
"rows": 3000,
"cost": 4.465185951
},
{
"substitute_best_equal": {
"condition": "WHERE",
"resulting_condition": "a.c2 = 'c2_01'"
}
},
{
"attaching_conditions_to_tables": {
"attached_conditions_computation": [],
"attached_conditions_summary": [
{
"table": "a",
"attached_condition": "a.c2 = 'c2_01'"
}
]
}
},
{
"make_join_readinfo": []
},
{
"test_if_skip_sort_order": [
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "GROUP BY",
"table": "a",
"rows_estimation": 3000,
"filesort_cost": 0.356339442,
"read_cost": 2.845355042,
"filesort_type": "merge_sort with addon fields",
"fanout": 1,
"possible_keys": [
{
"index": "PRIMARY",
"can_resolve_order": false,
"cause": "not usable index for the query"
},
{
"index": "indx01",
"can_resolve_order": false,
"cause": "not usable index for the query"
},
{
"index": "indx02",
"can_resolve_order": true,
"direction": 1,
"rows_to_examine": 14700,
"range_scan": false,
"scan_cost": 14.74536712,
"usable": false,
"cause": "cost"
},
{
"index": "indx03",
"can_resolve_order": false,
"cause": "not usable index for the query"
},
{
"index": "indx04",
"can_resolve_order": false,
"cause": "not usable index for the query"
}
]
}
}
]
}
]
}
},
{
"join_execution": {
"select_id": 1,
"steps": []
}
}
]
}