Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.0.26
-
None
-
Debian amd64
Description
After upgrade from 10.0.20 to 10.0.26 some queries become many times slower because of optimizer.
Table definition:
CREATE TABLE `versioned_params` ( |
`object_id` int(10) unsigned NOT NULL, |
`param_id` int(10) unsigned NOT NULL, |
`time_id` int(10) unsigned NOT NULL, |
`counter` int(10) unsigned NOT NULL AUTO_INCREMENT, |
`device_time` datetime(3) NOT NULL, |
`value` mediumblob,
|
PRIMARY KEY (`object_id`,`time_id`,`param_id`,`counter`), |
KEY `second_idx` (`object_id`,`param_id`,`device_time`,`time_id`) |
) ENGINE=TokuDB
|
Query:
EXPLAIN SELECT object_id, param_id, MAX(device_time) AS device_time FROM versioned_params WHERE 1 AND object_id IN (0,1782) AND device_time < '2016-08-08 16:00:00.000' GROUP BY object_id, param_id; |
Result on 10.0.20:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | versioned_params | range | PRIMARY,second_idx | second_idx | 15 | NULL | 1 | Using where; Using index for group-by |
Result on 10.0.26:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | versioned_params | range | PRIMARY,second_idx | second_idx | 4 | NULL | 8957720 | Using where; Using index |