|
In some circumstances EXPLAIN does not report the correct index being chosen for a given query. It seems to happen when there are more than one composite index that start with the same column(s).
This can be seen in the optimizer trace.
MariaDB [test]> CREATE TABLE `tritest` (
|
`Id` bigint(20) NOT NULL AUTO_INCREMENT,
|
`IdDispositivo` int(11) DEFAULT NULL,
|
`TipoEvento` int(11) DEFAULT NULL,
|
`Fecha` bigint(20) DEFAULT NULL,
|
PRIMARY KEY (`Id`),
|
KEY `TipoEvento` (`TipoEvento`),
|
KEY `IdDispositivo` (`IdDispositivo`,`TipoEvento`,`Fecha`),
|
KEY `IdDispositivo_2` (`IdDispositivo`,`Fecha`)
|
) ENGINE=InnoDB AUTO_INCREMENT=26952849249 DEFAULT CHARSET=latin1;
|
Query OK, 0 rows affected (0.000 sec)
|
|
[ LOAD ATTACHED DUMP ]
|
|
MariaDB [test]> set optimizer_trace=1;
|
Query OK, 0 rows affected (0.000 sec)
|
|
MariaDB [test]> explain select Id from tritest where IdDispositivo = 707043 AND Fecha >= 1665126000000 order by Fecha DESC, Id DESC LIMIT 0,1\G
|
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: tritest
|
type: ref
|
possible_keys: IdDispositivo,IdDispositivo_2
|
key: IdDispositivo
|
key_len: 5
|
ref: const
|
rows: 1
|
Extra: Using where; Using index; Using filesort
|
1 row in set (0.000 sec)
|
|
MariaDB [test]> select Id from tritest where IdDispositivo = 707043 AND Fecha >= 1665126000000 order by Fecha DESC, Id DESC LIMIT 0,1\G
|
*************************** 1. row ***************************
|
Id: 26952846959
|
1 row in set (0.000 sec)
|
|
MariaDB [test]> select * from information_schema.optimizer_trace\G
|
*************************** 1. row ***************************
|
QUERY: select Id from tritest where IdDispositivo = 707043 AND Fecha >= 1665126000000 order by Fecha DESC, Id DESC LIMIT 0,1
|
TRACE: {
|
"steps": [
|
{
|
"join_preparation": {
|
"select_id": 1,
|
"steps": [
|
{
|
"expanded_query": "select tritest.`Id` AS `Id` from tritest where tritest.IdDispositivo = 707043 and tritest.Fecha >= 1665126000000 order by tritest.Fecha desc,tritest.`Id` desc limit 0,1"
|
}
|
]
|
}
|
},
|
{
|
"join_optimization": {
|
"select_id": 1,
|
"steps": [
|
{
|
"condition_processing": {
|
"condition": "WHERE",
|
"original_condition": "tritest.IdDispositivo = 707043 and tritest.Fecha >= 1665126000000",
|
"steps": [
|
{
|
"transformation": "equality_propagation",
|
"resulting_condition": "tritest.Fecha >= 1665126000000 and multiple equal(707043, tritest.IdDispositivo)"
|
},
|
{
|
"transformation": "constant_propagation",
|
"resulting_condition": "tritest.Fecha >= 1665126000000 and multiple equal(707043, tritest.IdDispositivo)"
|
},
|
{
|
"transformation": "trivial_condition_removal",
|
"resulting_condition": "tritest.Fecha >= 1665126000000 and multiple equal(707043, tritest.IdDispositivo)"
|
}
|
]
|
}
|
},
|
{
|
"table_dependencies": [
|
{
|
"table": "tritest",
|
"row_may_be_null": false,
|
"map_bit": 0,
|
"depends_on_map_bits": []
|
}
|
]
|
},
|
{
|
"ref_optimizer_key_uses": [
|
{
|
"table": "tritest",
|
"field": "IdDispositivo",
|
"equals": "707043",
|
"null_rejecting": true
|
},
|
{
|
"table": "tritest",
|
"field": "IdDispositivo",
|
"equals": "707043",
|
"null_rejecting": true
|
}
|
]
|
},
|
{
|
"rows_estimation": [
|
{
|
"table": "tritest",
|
"range_analysis": {
|
"table_scan": {
|
"rows": 1000,
|
"cost": 207
|
},
|
"potential_range_indexes": [
|
{
|
"index": "PRIMARY",
|
"usable": false,
|
"cause": "not applicable"
|
},
|
{
|
"index": "TipoEvento",
|
"usable": false,
|
"cause": "not applicable"
|
},
|
{
|
"index": "IdDispositivo",
|
"usable": true,
|
"key_parts": ["IdDispositivo", "TipoEvento", "Fecha", "Id"]
|
},
|
{
|
"index": "IdDispositivo_2",
|
"usable": true,
|
"key_parts": ["IdDispositivo", "Fecha", "Id"]
|
}
|
],
|
"best_covering_index_scan": {
|
"index": "IdDispositivo_2",
|
"cost": 202.2685383,
|
"chosen": true
|
},
|
"setup_range_conditions": [],
|
"analyzing_range_alternatives": {
|
"range_scan_alternatives": [
|
{
|
"index": "IdDispositivo",
|
"ranges": ["(707043) <= (IdDispositivo) <= (707043)"],
|
"rowid_ordered": false,
|
"using_mrr": false,
|
"index_only": true,
|
"rows": 1,
|
"cost": 0.34532957,
|
"chosen": true
|
},
|
{
|
"index": "IdDispositivo_2",
|
"ranges": [
|
"(707043,1665126000000) <= (IdDispositivo,Fecha) <= (707043)"
|
],
|
"rowid_ordered": false,
|
"using_mrr": false,
|
"index_only": true,
|
"rows": 1,
|
"cost": 0.345268538,
|
"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": "IdDispositivo_2",
|
"rows": 1,
|
"ranges": [
|
"(707043,1665126000000) <= (IdDispositivo,Fecha) <= (707043)"
|
]
|
},
|
"rows_for_plan": 1,
|
"cost_for_plan": 0.345268538,
|
"chosen": true
|
}
|
}
|
},
|
{
|
"table": "tritest",
|
"rowid_filters": [
|
{
|
"key": "IdDispositivo_2",
|
"build_cost": 0.130268538,
|
"rows": 1
|
},
|
{
|
"key": "IdDispositivo_2",
|
"build_cost": 0.130268538,
|
"rows": 1
|
}
|
]
|
},
|
{
|
"selectivity_for_indexes": [
|
{
|
"index_name": "IdDispositivo_2",
|
"selectivity_from_index": 0.001
|
}
|
],
|
"selectivity_for_columns": [],
|
"cond_selectivity": 0.001
|
}
|
]
|
},
|
{
|
"considered_execution_plans": [
|
{
|
"plan_prefix": [],
|
"table": "tritest",
|
"best_access_path": {
|
"considered_access_paths": [
|
{
|
"access_type": "ref",
|
"index": "IdDispositivo",
|
"used_range_estimates": true,
|
"rows": 1,
|
"cost": 0.12532957,
|
"chosen": true
|
},
|
{
|
"access_type": "ref",
|
"index": "IdDispositivo_2",
|
"rows": 1,
|
"cost": 1.000268538,
|
"chosen": false,
|
"cause": "cost"
|
},
|
{
|
"access_type": "range",
|
"resulting_rows": 1,
|
"cost": 0.345268538,
|
"chosen": false
|
}
|
],
|
"chosen_access_method": {
|
"type": "ref",
|
"records": 1,
|
"cost": 0.12532957,
|
"uses_join_buffering": false
|
}
|
},
|
"rows_for_plan": 1,
|
"cost_for_plan": 0.32532957,
|
"estimated_join_cardinality": 1
|
}
|
]
|
},
|
{
|
"best_join_order": ["tritest"]
|
},
|
{
|
"attaching_conditions_to_tables": {
|
"original_condition": "tritest.IdDispositivo = 707043 and tritest.Fecha >= 1665126000000",
|
"attached_conditions_computation": [],
|
"attached_conditions_summary": [
|
{
|
"table": "tritest",
|
"attached": "tritest.Fecha >= 1665126000000"
|
}
|
]
|
}
|
},
|
{
|
"reconsidering_access_paths_for_index_ordering": {
|
"clause": "ORDER BY",
|
"fanout": 1,
|
"read_time": 0.12632957,
|
"table": "tritest",
|
"rows_estimation": 1,
|
"possible_keys": [
|
{
|
"index": "PRIMARY",
|
"can_resolve_order": false,
|
"cause": "not usable index for the query"
|
},
|
{
|
"index": "TipoEvento",
|
"can_resolve_order": false,
|
"cause": "not usable index for the query"
|
},
|
{
|
"index": "IdDispositivo",
|
"can_resolve_order": false,
|
"cause": "order can not be resolved by key"
|
},
|
{
|
"index": "IdDispositivo_2",
|
"can_resolve_order": true,
|
"updated_limit": 1000,
|
"range_scan_time": 0.345268538,
|
"index_scan_time": 0.345268538,
|
"usable": false,
|
"cause": "cost"
|
}
|
]
|
}
|
}
|
]
|
}
|
},
|
{
|
"join_execution": {
|
"select_id": 1,
|
"steps": []
|
}
|
}
|
]
|
}
|
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
|
INSUFFICIENT_PRIVILEGES: 0
|
1 row in set (0.000 sec)
|
You can see above that EXPLAIN says the chosen index will be IdDispositivo, but in reality IdDispositivo2 is used as it has a lower cost.
Reproducible with attached dump.
|
tritest.sql