|
Given a table
CREATE TABLE `company_naf` (
|
`uri` varchar(255) NOT NULL,
|
`partOfNafClassification` varchar(255) NOT NULL,
|
`sirenCode` int(10) unsigned NOT NULL,
|
`label` varchar(255) NOT NULL,
|
`postalCode` varchar(255) NOT NULL,
|
`slug` varchar(255) NOT NULL,
|
PRIMARY KEY (`uri`),
|
KEY `partOfNafClassification_sirenCode` (`partOfNafClassification`,`sirenCode`),
|
KEY `partOfNafClassification_sirenCode_desc` (`partOfNafClassification`,`sirenCode` DESC)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
|
Slow query :
analyze format=json SELECT uri ,label FROM company_naf WHERE partOfNafClassification = "eco/NafClassification/6820B" AND sirenCode < 822080040 ORDER BY sirenCode DESC LIMIT 7;
|
|
{
|
"query_optimization": {
|
"r_total_time_ms": 0.334227946
|
},
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 1793.478093,
|
"nested_loop": [
|
{
|
"table": {
|
"table_name": "company_naf",
|
"access_type": "ref",
|
"possible_keys": [
|
"partOfNafClassification_sirenCode",
|
"partOfNafClassification_sirenCode_desc"
|
],
|
"key": "partOfNafClassification_sirenCode",
|
"key_length": "767",
|
"used_key_parts": ["partOfNafClassification"],
|
"ref": ["const"],
|
"r_loops": 1,
|
"rows": 2308924,
|
"r_rows": 626151,
|
"r_table_time_ms": 1588.861929,
|
"r_other_time_ms": 204.6085331,
|
"r_engine_stats": {
|
"pages_accessed": 1880728
|
},
|
"filtered": 16.34165764,
|
"r_filtered": 0.001117941,
|
"attached_condition": "company_naf.partOfNafClassification <=> 'eco/NafClassification/6820B' and company_naf.partOfNafClassification = 'eco/NafClassification/6820B' and company_naf.sirenCode < 822080040"
|
}
|
}
|
]
|
}
|
}
|
1 row in set (1.788 sec)
|
Ref access do not apply order by limit optimisation while a range do it as in following plan
FAST using ASC index just with removing VARCHAR(255) label column range is used
MariaDB [directory_company]> analyze format=json SELECT uri FROM company_naf WHERE partOfNafClassification = "eco/NafClassification/6820B" AND sirenCode < 822080040 ORDER BY sirenCode DESC
|
LIMIT 7 ;
|
{
|
"query_optimization": {
|
"r_total_time_ms": 0.344385076
|
},
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 0.077455994,
|
"nested_loop": [
|
{
|
"table": {
|
"table_name": "company_naf",
|
"access_type": "range",
|
"possible_keys": [
|
"partOfNafClassification_sirenCode",
|
"partOfNafClassification_sirenCode_desc"
|
],
|
"key": "partOfNafClassification_sirenCode",
|
"key_length": "771",
|
"used_key_parts": ["partOfNafClassification", "sirenCode"],
|
"r_loops": 1,
|
"rows": 2308924,
|
"r_rows": 7,
|
"r_table_time_ms": 0.038314642,
|
"r_other_time_ms": 0.028648057,
|
"r_engine_stats": {
|
"pages_accessed": 4
|
},
|
"filtered": 16.34165764,
|
"r_filtered": 100,
|
"attached_condition": "company_naf.partOfNafClassification = 'eco/NafClassification/6820B' and company_naf.sirenCode < 822080040",
|
"using_index": true
|
}
|
}
|
]
|
}
|
}
|
1 row in set (0.001 sec)
|
Fast with column label and adding PK in the ORDER it trigger usage of the DESC index using range:
analyze format=json SELECT uri ,label FROM company_naf WHERE partOfNafClassification = "eco/NafClassification/6820B" AND sirenCode < 822080040 ORDER BY sirenCode DESC , uri LIMIT 7;
|
{
|
"query_optimization": {
|
"r_total_time_ms": 0.416889024
|
},
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 0.152188866,
|
"nested_loop": [
|
{
|
"table": {
|
"table_name": "company_naf",
|
"access_type": "range",
|
"possible_keys": [
|
"partOfNafClassification_sirenCode",
|
"partOfNafClassification_sirenCode_desc"
|
],
|
"key": "partOfNafClassification_sirenCode_desc",
|
"key_length": "771",
|
"used_key_parts": ["partOfNafClassification"],
|
"r_loops": 1,
|
"rows": 3191114,
|
"r_rows": 7,
|
"r_table_time_ms": 0.119271142,
|
"r_other_time_ms": 0.021237801,
|
"r_engine_stats": {
|
"pages_accessed": 42
|
},
|
"filtered": 16.34165764,
|
"r_filtered": 100,
|
"attached_condition": "company_naf.partOfNafClassification <=> 'eco/NafClassification/6820B' and company_naf.partOfNafClassification = 'eco/NafClassification/6820B' and company_naf.sirenCode < 822080040"
|
}
|
}
|
]
|
}
|
}
|
1 row in set (0.001 sec)
|
|