Why MySQL optimizer does not use all three columns of the index?

Percona MySQL 5.7

the table schema:
CREATE TABLE Developer.Rate (
 ID bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
 TIME datetime NOT NULL,
 BASE varchar(3) NOT NULL,
 QUOTE varchar(3) NOT NULL,
 BID double NOT NULL,
 ASK double NOT NULL,
 PRIMARY KEY (ID),
 IDX_TIME INDEX (TIME)
 UNIQUE INDEX IDX_UK (BASE, QUOTE, TIME)
)
ENGINE = INNODB
ROW_FORMAT = COMPRESSED; 


Request the most recent data until the specified period, uses the full unique key of 2 columns of three.

if you make a request in the usual way:

EXPLAIN FORMAT=JSON
SELECT
BID
FROM 
Rate
WHERE 
 BASE = 'EUR' 
 AND QUOTE = 'USD' 
 AND `TIME` <= (NOW() - INTERVAL 1 MONTH) 
ORDER BY 
 `TIME` DESC 
LIMIT 1
;


the explain shows that the index uses only 2 of the first column of a unique index: BASE QUOTE
{
 "query_block": {
 "select_id": 1,
 "cost_info": {
 "query_cost": "10231052.40"
},
 "ordering_operation": {
 "using_filesort": false,
 "table": {
 "table_name": "Rate",
 "access_type": "ref",
 "possible_keys": [
"IDX_UK",
"IDX_TIME"
],
 "key": "IDX_UK",
 "used_key_parts": [
"BASE",
"QUOTE"
],
 "key_length": "22",
 "ref": [
"const",
"const"
],
 "rows_examined_per_scan": 45966462,
 "rows_produced_per_join": 22983231,
 "filtered": "50.00",
 "cost_info": {
 "read_cost": "1037760.00",
 "eval_cost": "4596646.20",
 "prefix_cost": "10231052.40",
 "data_read_per_join": "1G"
},
 "used_columns": [
"ID",
"TIME",
"BASE",
"QUOTE",
BID
],
 "attached_condition": "((`Developer`.`Rate`.`BASE` <=> 'EUR') and (`Developer`.`Rate`.`QUOTE` <=> 'USD') and (`Developer`.`Rate`.`TIME` <= <cache>((now() - interval 1 month))))"
}
}
}
}</cache>


but if you force the optimizer to use the index IDX_UK, muscul begins to use all three columns in the query

EXPLAIN FORMAT=JSON
SELECT
BID
FROM 
 Rate FORCE INDEX(IDX_UK)
WHERE 
 BASE = 'EUR' 
 AND QUOTE = 'USD' 
 AND `TIME` <= (NOW() - INTERVAL 1 MONTH) 
ORDER BY 
 `TIME` DESC 
LIMIT 1

{
 "query_block": {
 "select_id": 1,
 "cost_info": {
 "query_cost": "10231052.40"
},
 "ordering_operation": {
 "using_filesort": false,
 "table": {
 "table_name": "Rate",
 "access_type": "range",
 "possible_keys": [
"IDX_UK"
],
 "key": "IDX_UK",
 "used_key_parts": [
"BASE",
"QUOTE",
"TIME"
],
 "key_length": "27",
 "rows_examined_per_scan": 45966462,
 "rows_produced_per_join": 15320621,
 "filtered": "100.00",
 "index_condition": "((`Developer`.`Rate`.`BASE` = 'EUR') and (`Developer`.`Rate`.`QUOTE` = 'USD') and (`Developer`.`Rate`.`TIME` <= <cache>((now() - interval 1 month))))",
 "cost_info": {
 "read_cost": "1037760.00",
 "eval_cost": "3064124.31",
 "prefix_cost": "10231052.40",
 "data_read_per_join": "818M"
},
 "used_columns": [
"ID",
"TIME",
"BASE",
"QUOTE",
BID
]
}
}
}
}</cache>


why without an explicit index does not want to use all three columns of this index? Do IDX_TIME more than IDX_UK like the optimizer?
June 7th 19 at 14:34
0 answer

Find more questions by tags Optimization of SQL-queriesPercona