Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6.11, 10.6.12
-
None
-
None
-
FreeBSD 12.3
Description
The bug is partially intersects with MDEV-26256
Table definition (seemingly unrelated fields replaced with placeholders):
CREATE TABLE `ttt` ( |
`id` int(11) NOT NULL AUTO_INCREMENT, |
`id2` int(11) NOT NULL DEFAULT 0, |
`ctime` int(11) NOT NULL DEFAULT 0, |
`f1` varchar(8192) NOT NULL DEFAULT '', |
`f2` int(11) NOT NULL DEFAULT 0, |
`f3` int(11) NOT NULL DEFAULT 0, |
`f4` int(11) NOT NULL DEFAULT 0, |
`f5` int(11) NOT NULL DEFAULT 0, |
`f6` int(11) NOT NULL DEFAULT 0, |
`f7` int(11) NOT NULL DEFAULT 0, |
`f8` int(11) NOT NULL DEFAULT 0, |
`f9` int(11) NOT NULL DEFAULT 0, |
`f10` int(11) NOT NULL DEFAULT 0, |
`f11` int(11) NOT NULL DEFAULT 0, |
`f12` int(11) NOT NULL DEFAULT 0, |
`f13` int(11) NOT NULL DEFAULT 0, |
`f14` varchar(8192) NOT NULL DEFAULT '', |
`f15` varchar(8192) NOT NULL DEFAULT '', |
PRIMARY KEY (`id`), |
KEY `id2_ctime` (`id2`,`ctime`), |
KEY `time` (`ctime`) |
) ENGINE=MyISAM AUTO_INCREMENT=8238691 DEFAULT CHARSET=cp1251 COLLATE=cp1251_general_ci; |
Query:
SELECT * FROM `ttt` WHERE `id2`='8' AND `ctime`>TTTTTT ORDER BY `ctime` DESC LIMIT 10; |
Where TTT is some value. The 'WHERE' condition never covers more than 20 rows, and, as you may see, WHERE+ORDER perfectly matches `id2_ctime` index. Usually, it handled normally via the mentioned index:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | ttt | range | id2_ctime,time | id2_ctime | 8 | NULL | 8 | Using where |
Sometimes (looks like pure random, <1% of requests), things goes wrong:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | ttt | ref | id2_ctime,time | id2_ctime | 4 | const | 1025552 | Using where |
It stripped the half of useful index and bruteforced rows that matched only the first part of the index.
I got optimizer traces for 'good' and 'bad' cases (attached). There is two interesting parts:
1) wrong rows estimation (more specific, longer key gets 74 rows while shorted `ctime`-only key gets 72 rows) - this looks like the same issue MDEV-26256
2) after rows_estimation, the next step "considered_execution_plans": here we see that rows_estimation results seems ignored (i don't see the plan using "better" `ctime`-only key here), but instead now we comparing the plan using `id2_ctime` key and the plan using its truncated version `id2`-only key.
"considered_access_paths": [
|
{
|
"access_type": "ref",
|
"index": "id2_ctime",
|
"rows": 1025552,
|
"cost": 824471.8898,
|
"chosen": true
|
},
|
{
|
"access_type": "range",
|
"resulting_rows": 663.3084173,
|
"cost": 1.79769e308,
|
"chosen": false
|
}
|
],
|
"chosen_access_method": {
|
"type": "ref",
|
"records": 1025552,
|
"cost": 824471.8898,
|
"uses_join_buffering": false
|
}
|
For some reason, long-key version gets completely wrong resulting_rows=663.3084173, and yet more irrelevant cost=1.79769e308. Due to this, truncated-key version with it properly calculated bad cost=824471 wins. I don't think that the "1.79769e308" value may be the result of any normal calculations, it looks like the result of something completely wrong.