|
Consider the following test on a table with some data in it:
MariaDB [(none)]> select version();
|
+-------------------------------------+
|
| version() |
|
+-------------------------------------+
|
| 10.6.8-MariaDB-1:10.6.8+maria~focal |
|
+-------------------------------------+
|
1 row in set (0.000 sec)
|
|
MariaDB [(none)]> use data_pool;
|
Reading table information for completion of table and column names
|
You can turn off this feature to get a quicker startup with -A
|
|
Database changed
|
MariaDB [data_pool]> show create table action_integer\G
|
*************************** 1. row ***************************
|
Table: action_integer
|
Create Table: CREATE TABLE `action_integer` (
|
`id` bigint(20) NOT NULL,
|
`cr_date` datetime(6) DEFAULT NULL,
|
`element_date` datetime(6) DEFAULT NULL,
|
`element_id` bigint(20) DEFAULT NULL,
|
`event_date` datetime(6) DEFAULT NULL,
|
`parent_event` bigint(20) DEFAULT NULL,
|
`parent_type` varchar(1) DEFAULT NULL,
|
`source_device` varchar(16) DEFAULT NULL,
|
`source_name_id` varchar(16) DEFAULT NULL,
|
`source_type` varchar(16) DEFAULT NULL,
|
`type` smallint(6) NOT NULL,
|
`e_integer` bigint(20) DEFAULT NULL,
|
PRIMARY KEY (`id`,`type`),
|
KEY `element_id_event_date_index` (`element_id`,`event_date`),
|
KEY `type_e_integer_index` (`type`,`e_integer`),
|
KEY `parent_event_event_date_index` (`parent_event`,`event_date`),
|
KEY `type_element_id_event_date_index` (`type`,`element_id`,`event_date`),
|
KEY `event_date_type_e_integer_index` (`event_date`,`type`,`e_integer`),
|
KEY `element_id_index` (`element_id`),
|
KEY `event_date_index` (`event_date`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
PARTITION BY HASH (`type`)
|
PARTITIONS 40
|
1 row in set (0.000 sec)
|
|
MariaDB [data_pool]> set session optimizer_switch='rowid_filter=OFF';
|
Query OK, 0 rows affected (0.000 sec)
|
|
MariaDB [data_pool]> set session optimizer_use_condition_selectivity = 1;
|
Query OK, 0 rows affected (0.000 sec)
|
|
MariaDB [data_pool]> show session variables like 'optimizer%';
|
+--------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Variable_name | Value |
|
+--------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| optimizer_max_sel_arg_weight | 32000 |
|
| optimizer_prune_level | 1 |
|
| optimizer_search_depth | 62 |
|
| optimizer_selectivity_sampling_limit | 100 |
|
| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=off,condition_pushdown_from_having=on,not_null_range_scan=off |
|
| optimizer_trace | enabled=off |
|
| optimizer_trace_max_mem_size | 1048576 |
|
| optimizer_use_condition_selectivity | 1 |
|
+--------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
8 rows in set (0.001 sec)
|
|
MariaDB [data_pool]> analyze format = json select element_id from action_integer
|
-> where event_date
|
-> between '2022-01-02 11:00:00' and '2022-01-02 11:10:00'
|
-> and type = 25
|
-> and e_integer >= 3000
|
-> order by event_date desc;
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| ANALYZE |
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| {
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 0.748598657,
|
"table": {
|
"table_name": "action_integer",
|
"partitions": ["p25"],
|
"access_type": "range",
|
"possible_keys": [
|
"type_e_integer_index",
|
"type_element_id_event_date_index",
|
"event_date_type_e_integer_index",
|
"event_date_index"
|
],
|
"key": "event_date_index",
|
"key_length": "9",
|
"used_key_parts": ["event_date"],
|
"r_loops": 1,
|
"rows": 1,
|
"r_rows": 0,
|
"r_table_time_ms": 0.709776119,
|
"r_other_time_ms": 0.021946347,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "action_integer.`type` = 25 and action_integer.event_date between '2022-01-02 11:00:00.000000' and '2022-01-02 11:10:00.000000' and action_integer.e_integer >= 3000"
|
}
|
}
|
} |
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.017 sec)
|
Now compare to this result with other index FORCEd, in the same session:
MariaDB [data_pool]> analyze format = json select element_id from action_integer
|
-> FORCE INDEX (event_date_type_e_integer_index)
|
-> where event_date
|
-> between '2022-01-02 11:00:00' and '2022-01-02 11:10:00'
|
-> and type = 25
|
-> and e_integer >= 3000
|
-> order by event_date desc;
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| ANALYZE |
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| {
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 0.053786246,
|
"table": {
|
"table_name": "action_integer",
|
"partitions": ["p25"],
|
"access_type": "range",
|
"possible_keys": ["event_date_type_e_integer_index"],
|
"key": "event_date_type_e_integer_index",
|
"key_length": "20",
|
"used_key_parts": ["event_date", "type", "e_integer"],
|
"r_loops": 1,
|
"rows": 1,
|
"r_rows": 0,
|
"r_table_time_ms": 0.026492428,
|
"r_other_time_ms": 0.014467001,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "action_integer.`type` = 25 and action_integer.event_date between '2022-01-02 11:00:00.000000' and '2022-01-02 11:10:00.000000' and action_integer.e_integer >= 3000"
|
}
|
}
|
} |
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.001 sec)
|
Note more key parts used and faster execution. Here is the optimizer trace:
MariaDB [data_pool]> set session optimizer_trace='enabled=on';
|
Query OK, 0 rows affected (0.000 sec)
|
|
MariaDB [data_pool]> analyze format = json select element_id from action_integer
|
-> where event_date
|
-> between '2022-01-02 11:00:00' and '2022-01-02 11:10:00'
|
-> and type = 25
|
-> and e_integer >= 3000
|
-> order by event_date desc;
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| ANALYZE |
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| {
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 0.074182264,
|
"table": {
|
"table_name": "action_integer",
|
"partitions": ["p25"],
|
"access_type": "range",
|
"possible_keys": [
|
"type_e_integer_index",
|
"type_element_id_event_date_index",
|
"event_date_type_e_integer_index",
|
"event_date_index"
|
],
|
"key": "event_date_index",
|
"key_length": "9",
|
"used_key_parts": ["event_date"],
|
"r_loops": 1,
|
"rows": 1,
|
"r_rows": 0,
|
"r_table_time_ms": 0.034520477,
|
"r_other_time_ms": 0.020919179,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "action_integer.`type` = 25 and action_integer.event_date between '2022-01-02 11:00:00.000000' and '2022-01-02 11:10:00.000000' and action_integer.e_integer >= 3000"
|
}
|
}
|
} |
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.001 sec)
|
|
MariaDB [data_pool]> select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
|
*************************** 1. row ***************************
|
QUERY: analyze format = json select element_id from action_integer
|
where event_date
|
between '2022-01-02 11:00:00' and '2022-01-02 11:10:00'
|
and type = 25
|
and e_integer >= 3000
|
order by event_date desc
|
TRACE: {
|
"steps": [
|
{
|
"join_preparation": {
|
"select_id": 1,
|
"steps": [
|
{
|
"expanded_query": "select action_integer.element_id AS element_id from action_integer where action_integer.event_date between <cache>('2022-01-02 11:00:00') and <cache>('2022-01-02 11:10:00') and action_integer.`type` = 25 and action_integer.e_integer >= 3000 order by action_integer.event_date desc"
|
}
|
]
|
}
|
},
|
{
|
"join_optimization": {
|
"select_id": 1,
|
"steps": [
|
{
|
"condition_processing": {
|
"condition": "WHERE",
|
"original_condition": "action_integer.event_date between <cache>('2022-01-02 11:00:00') and <cache>('2022-01-02 11:10:00') and action_integer.`type` = 25 and action_integer.e_integer >= 3000",
|
"steps": [
|
{
|
"transformation": "equality_propagation",
|
"resulting_condition": "action_integer.event_date between <cache>('2022-01-02 11:00:00') and <cache>('2022-01-02 11:10:00') and action_integer.e_integer >= 3000 and multiple equal(25, action_integer.`type`)"
|
},
|
{
|
"transformation": "constant_propagation",
|
"resulting_condition": "action_integer.event_date between <cache>('2022-01-02 11:00:00') and <cache>('2022-01-02 11:10:00') and action_integer.e_integer >= 3000 and multiple equal(25, action_integer.`type`)"
|
},
|
{
|
"transformation": "trivial_condition_removal",
|
"resulting_condition": "action_integer.event_date between <cache>('2022-01-02 11:00:00') and <cache>('2022-01-02 11:10:00') and action_integer.e_integer >= 3000 and multiple equal(25, action_integer.`type`)"
|
}
|
]
|
}
|
},
|
{
|
"table_dependencies": [
|
{
|
"table": "action_integer",
|
"row_may_be_null": false,
|
"map_bit": 0,
|
"depends_on_map_bits": []
|
}
|
]
|
},
|
{
|
"ref_optimizer_key_uses": [
|
{
|
"table": "action_integer",
|
"field": "type",
|
"equals": "25",
|
"null_rejecting": false
|
},
|
{
|
"table": "action_integer",
|
"field": "type",
|
"equals": "25",
|
"null_rejecting": false
|
}
|
]
|
},
|
{
|
"rows_estimation": [
|
{
|
"table": "action_integer",
|
"range_analysis": {
|
"table_scan": {
|
"rows": 23678726,
|
"cost": 4940291.2
|
},
|
"potential_range_indexes": [
|
{
|
"index": "PRIMARY",
|
"usable": false,
|
"cause": "not applicable"
|
},
|
{
|
"index": "element_id_event_date_index",
|
"usable": false,
|
"cause": "not applicable"
|
},
|
{
|
"index": "type_e_integer_index",
|
"usable": true,
|
"key_parts": ["type", "e_integer"]
|
},
|
{
|
"index": "parent_event_event_date_index",
|
"usable": false,
|
"cause": "not applicable"
|
},
|
{
|
"index": "type_element_id_event_date_index",
|
"usable": true,
|
"key_parts": ["type", "element_id", "event_date"]
|
},
|
{
|
"index": "event_date_type_e_integer_index",
|
"usable": true,
|
"key_parts": ["event_date", "type", "e_integer"]
|
},
|
{
|
"index": "element_id_index",
|
"usable": false,
|
"cause": "not applicable"
|
},
|
{
|
"index": "event_date_index",
|
"usable": true,
|
"key_parts": ["event_date"]
|
}
|
],
|
"setup_range_conditions": [],
|
"analyzing_range_alternatives": {
|
"range_scan_alternatives": [
|
{
|
"index": "type_e_integer_index",
|
"ranges": ["(25,3000) <= (type,e_integer) <= (25)"],
|
"rowid_ordered": false,
|
"using_mrr": false,
|
"index_only": false,
|
"rows": 2005818,
|
"cost": 2411243.9,
|
"chosen": true
|
},
|
{
|
"index": "type_element_id_event_date_index",
|
"ranges": ["(25) <= (type) <= (25)"],
|
"rowid_ordered": false,
|
"using_mrr": false,
|
"index_only": false,
|
"rows": 11839363,
|
"cost": 14242321.18,
|
"chosen": false,
|
"cause": "cost"
|
},
|
{
|
"index": "event_date_type_e_integer_index",
|
"ranges": [
|
"(2022-01-02 11:00:00.000000,25,3000) <= (event_date,type,e_integer) <= (2022-01-02 11:10:00.000000,25)"
|
],
|
"rowid_ordered": false,
|
"using_mrr": false,
|
"index_only": false,
|
"rows": 1,
|
"cost": 1.345366189,
|
"chosen": true
|
},
|
{
|
"index": "event_date_index",
|
"ranges": [
|
"(2022-01-02 11:00:00.000000) <= (event_date) <= (2022-01-02 11:10:00.000000)"
|
],
|
"rowid_ordered": false,
|
"using_mrr": false,
|
"index_only": false,
|
"rows": 1,
|
"cost": 1.345231919,
|
"chosen": true
|
}
|
],
|
"analyzing_roworder_intersect": {
|
"cause": "too few roworder scans"
|
},
|
"analyzing_index_merge_union": []
|
},
|
"group_index_range": {
|
"chosen": false,
|
"cause": "no group by or distinct"
|
},
|
"chosen_range_access_summary": {
|
"range_access_plan": {
|
"type": "range_scan",
|
"index": "event_date_index",
|
"rows": 1,
|
"ranges": [
|
"(2022-01-02 11:00:00.000000) <= (event_date) <= (2022-01-02 11:10:00.000000)"
|
]
|
},
|
"rows_for_plan": 1,
|
"cost_for_plan": 1.345231919,
|
"chosen": true
|
}
|
}
|
}
|
]
|
},
|
{
|
"considered_execution_plans": [
|
{
|
"plan_prefix": [],
|
"table": "action_integer",
|
"best_access_path": {
|
"considered_access_paths": [
|
{
|
"access_type": "ref",
|
"index": "type_e_integer_index",
|
"rows": 31368248,
|
"cost": 622439.4657,
|
"chosen": true
|
},
|
{
|
"access_type": "ref",
|
"index": "type_element_id_event_date_index",
|
"used_range_estimates": true,
|
"rows": 11839363,
|
"cost": 11874448.56,
|
"chosen": false,
|
"cause": "cost"
|
},
|
{
|
"access_type": "range",
|
"resulting_rows": 1,
|
"cost": 1.345231919,
|
"chosen": true,
|
"use_tmp_table": true
|
}
|
],
|
"chosen_access_method": {
|
"type": "range",
|
"records": 1,
|
"cost": 1.345231919,
|
"uses_join_buffering": false
|
}
|
},
|
"rows_for_plan": 1,
|
"cost_for_plan": 1.545231919,
|
"cost_for_sorting": 1
|
}
|
]
|
},
|
{
|
"best_join_order": ["action_integer"]
|
},
|
{
|
"substitute_best_equal": {
|
"condition": "WHERE",
|
"resulting_condition": "action_integer.`type` = 25 and action_integer.event_date between '2022-01-02 11:00:00.000000' and '2022-01-02 11:10:00.000000' and action_integer.e_integer >= 3000"
|
}
|
},
|
{
|
"attaching_conditions_to_tables": {
|
"attached_conditions_computation": [],
|
"attached_conditions_summary": [
|
{
|
"table": "action_integer",
|
"attached": "action_integer.`type` = 25 and action_integer.event_date between '2022-01-02 11:00:00.000000' and '2022-01-02 11:10:00.000000' and action_integer.e_integer >= 3000"
|
}
|
]
|
}
|
}
|
]
|
}
|
},
|
{
|
"join_execution": {
|
"select_id": 1,
|
"steps": []
|
}
|
}
|
]
|
}
|
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
|
INSUFFICIENT_PRIVILEGES: 0
|
1 row in set (0.001 sec)
|
It seems cost estimation is too pessimistic for the best index we have for this query.
Note that with all default optimizer settings the plan is:
MariaDB [data_pool]> explain select element_id from action_integer
|
-> where event_date
|
-> between '2022-01-02 11:00:00' and '2022-01-02 11:10:00'
|
-> and type = 25
|
-> and e_integer >= 3000
|
-> order by event_date desc;
|
+------+-------------+----------------+------------+--------------------------------------------------------------------------------------------------------+---------------------------------------+---------+-------+--------------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+----------------+------------+--------------------------------------------------------------------------------------------------------+---------------------------------------+---------+-------+--------------+-------------------------------------------------+
|
| 1 | SIMPLE | action_integer | ref|filter | type_e_integer_index,type_element_id_event_date_index,event_date_type_e_integer_index,event_date_index | type_e_integer_index|event_date_index | 2|9 | const | 2005818 (0%) | Using where; Using filesort; Using rowid filter |
|
+------+-------------+----------------+------------+--------------------------------------------------------------------------------------------------------+---------------------------------------+---------+-------+--------------+-------------------------------------------------+
|
1 row in set (0.001 sec)
|
and it took around one minute to execute the query that way. So, I see two problems here:
1. With default settings ref|filter access path is preferred, much worse.
2. With non-default settings from the test case above it seems cost estimation for range access is somewhat wrong and we end up using suboptimal index for range access.
|