MySQL 5.7 strange perfomance reduction with order by ASC/DESC on partitioned table

Question

I have had some strange trouble when updated MySQL Server to version 5.7 (under Ubuntu 16.04 LTS).

Preambula: I have some table with a lot of records (~250 millions). This table, in short, has such a structure:

CREATE TABLE `device_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `device` int(11) DEFAULT NULL,
  `data` double NOT NULL DEFAULT '0',
  `utc` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`utc`,`id`),
  KEY `id` (`id`),
  KEY `idx_devutc` (`device`,`utc`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (`utc`)
(PARTITION y_min VALUES LESS THAN (1200000000) ENGINE = MyISAM,
 PARTITION y_121 VALUES LESS THAN (1210000000) ENGINE = MyISAM,
 PARTITION y_122 VALUES LESS THAN (1220000000) ENGINE = MyISAM,
...
...
...
 PARTITION y_167 VALUES LESS THAN (1670000000) ENGINE = MyISAM,
 PARTITION y_168 VALUES LESS THAN (1680000000) ENGINE = MyISAM,
 PARTITION y_169 VALUES LESS THAN (1690000000) ENGINE = MyISAM,
 PARTITION y_max VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */

So, why does it have such unusual primary key (UTC, ID)? Field ID is autoincrement and it along can be a primary key. Well, we need to partition the table by field UTC, and MySQL say that to do so, field UTC has to be a primary key or at least the first part of primary key. We can`t use UTC along as primary key, because devices can send data several times per second, so we must use such a strange primary key: (UTC, ID). This is OK.

We also have and index on this table by (device, utc). Why? Because we need to execute queries retrieving data by certain device for certain period of time, e.g.:

SELECT `utc`, `data` 
FROM `device_data` 
WHERE `device` = :DeviceId AND `utc` >= :UtcFrom AND `utc` < :UtcTo 
ORDER BY `utc`;

It's very fast because of the index (device, utc).

Ambula: After upgrading MySQL Server to version 5.7 (from 5.0 or 5.1, i'm not sure now) some queries become very slow (2-3 minutes instead of 100-200 millisesonds). Some small investigation has found a condition: query speed falls down, when the order of sorting is descendant.

This query is still fast (100 milllseconds):

SELECT `utc`, `data` 
FROM `device_data` 
WHERE `device` = :DeviceId AND `utc` >= :UtcFrom AND `utc` < :UtcTo 
ORDER BY `utc` ASC;

But this query is very slow (~180 seconds):

SELECT `utc`, `data` 
FROM `device_data` 
WHERE `device` = :DeviceId AND `utc` >= :UtcFrom AND `utc` < :UtcTo 
ORDER BY `utc` DESC;

With the same param values, of course. Under previous version of MySQL Server both queries were fast (100-200 milliseconds).

After two days of working hard I found some trick to avoid the problem:

SELECT `utc`, `data` 
FROM `device_data` 
WHERE `device` = :DeviceId AND `utc` >= :UtcFrom AND `utc` < :UtcTo 
ORDER BY -`utc` ASC;

(Attantion on minus sign before utc in ORDER BY)

This query is also fast, finishing in milliseconds and returning records in reverse order, as we need.

Question: What is the reason of such a strange MySQL behavior, and how can I fix it?

Answer 1

I'm not a MySQL developer so I don't know the full details.

However, after having battled this on their issue tracker, the best guess is that it's a regression caused by this fix added in 5.7.3:

Partitioning: Index condition pushdown did not work with partitioned tables. (Bug #17306882, Bug #70001)

This theory is emphasized by the fact that we were able to circumvent the problem by setting this in my.cnf:

optimizer_switch=index_condition_pushdown=off

One can try this without restarting by using SET [GLOBAL] optimizer_switch='index_condition_pushdown=off'.

We also looked at the ORDER BY -`utc` ASC approach but were scared off as it adds Using filesort to each execution plan.