To add to my confusion, in 10.5, if I add a limit to the below query, the query optimizer does choose to use a key on the query:
This substantially speeds up the query. This also seems kind of "hacky." Even if this was a reasonable way forward, it is not a versatile option. Particularly if I want to use the query in a sub select (which I do): "This version of MariaDB doesn't yet
support 'LIMIT & IN/ALL/ANY/SOME subquery'"
HI there,
Looking for some advice. A common query "structure" that performed well in 10.3 is showing a significant performance decrease in 10.5.24. The root cause seems to be 10.5's refusal to use a key.
The table in question is has a "key => value" like structure:
CREATE TABLE `cases_meta` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`case_id` int(10) unsigned NOT NULL,
`key` varchar(50) NOT NULL,
`value` mediumtext DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `key` (`key`),
KEY `fk_caes_meta_1` (`case_id`),
CONSTRAINT `fk_caes_meta_1` FOREIGN KEY (`case_id`) REFERENCES `cases` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
The query in question simply is looking for a `key` with a specific value:
SELECT * FROM
cases_meta AS cm
WHERE
cm.`key` = 'PAN_NUMBER'
AND
cm.`value` = '153566'
In this example, the table has 2.8 million records.
In 10.5, it skips the `key` key, and grinds through 2.8 million records:
ANALYZE: {
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 2743.92693,
"table": {
"table_name": "cm",
"access_type": "ALL",
"possible_keys": ["key"],
"r_loops": 1,
"rows": 1004130,
"r_rows": 2820048,
"r_table_time_ms": 2548.916341,
"r_other_time_ms": 195.0068087,
"filtered": 30.31918144,
"r_filtered": 0,
"attached_condition": "ddx_lab_801615.cm.`key` = 'PAN_NUMBER' and ddx_lab_801615.cm.`value` = '153566'"
}
}
}
In 10.3, it uses the `key` key, and reads through a radically smaller subset of tables to fetch the result:
ANALYZE: {
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 357.84,
"table": {
"table_name": "cm",
"access_type": "ref",
"possible_keys": ["key"],
"key": "key",
"key_length": "152",
"used_key_parts": ["key"],
"ref": ["const"],
"r_loops": 1,
"rows": 304484,
"r_rows": 155123,
"r_total_time_ms": 340.94,
"filtered": 100,
"r_filtered": 0,
"index_condition": "ddx_lab_801615.cm.`key` = 'PAN_NUMBER'",
"attached_condition": "ddx_lab_801615.cm.`value` = '153566'"
}
}
}
I'm not sure why 10.5 is taking this substandard approach. Adding a USE INDEX (`key`) seems to have no effect in 10.5 either, it just skips the index.
Any ideas?
Thanks,
Mike