MySQL Bugs: #56451: slowness of a sql query with an order by desc

Bug #56451	slowness of a sql query with an order by desc
Submitted:	1 Sep 2010 13:53	Modified:	14 Apr 2016 13:47
Reporter:	Valenti Jove	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	mysql-5.1-telco-7.0	OS:	Linux
Assigned to:	MySQL Verification Team	CPU Architecture:	Any
Tags:	7.0.16

Description:
Queries with DESC ordering are very slow.

In an innodb database with the same table, all the queries are lightning fast.

How to repeat:
DESC ordering:

mysql> select f as d from l where e=2781 and p=24159 and f>='2010-08-29 02:43:40' order by d desc;
+---------------------+
| d                   |
+---------------------+
| 2010-08-29 02:44:22 |
| 2010-08-29 02:43:40 |
+---------------------+
2 rows in set (1 min 28.31 sec)

mysql> explain select f as d from l where e=2781 and p=24159 and f>='2010-08-29 02:43:40' order by d desc;
+----+-------------+-------+-------+-------------------+-----------+---------+------+------+-----------------------------------+
| id | select_type | table | type  | possible_keys     | key       | key_len | ref  | rows | Extra                             |
+----+-------------+-------+-------+-------------------+-----------+---------+------+------+-----------------------------------+
|  1 | SIMPLE      | l     | range | I_EMP,I_F         | I_EMP     | 18      | NULL |   10 | Using where with pushed condition |
+----+-------------+-------+-------+-------------------+-----------+---------+------+------+-----------------------------------+
1 row in set (0.01 sec)

f is a datetime, e and p are ints.
I_EMP is an index with the (e,p,f) fields.
In this case, l is a table with 157M rows.
Same query against the same table in an innodb engine is as fast as its ASC equivalent.

ASC version:

mysql> select f as d from l where e=2781 and p=24159 and f>='2010-08-29 02:43:40' order by d asc;
+---------------------+
| d                   |
+---------------------+
| 2010-08-29 02:43:40 |
| 2010-08-29 02:44:22 |
+---------------------+
2 rows in set (0.00 sec)

Explain for this select is the same as the one of the query with the desc ordering.

Without the range:

mysql> select f as d from l where e=2781 and p=24159 and f='2010-08-29 02:43:40' order by d desc;
+---------------------+
| d                   |
+---------------------+
| 2010-08-29 02:43:40 |
+---------------------+
1 row in set (0.00 sec

As a subselect, no problem either:

mysql> select d from (select f as d from l where e=2781 and p=24159 and f>='2010-08-29 02:43:40') as t order by d desc;
+---------------------+
| d                   |
+---------------------+
| 2010-08-29 02:44:22 |
| 2010-08-29 02:43:40 |
+---------------------+
2 rows in set (0.00 sec)

I know the index is ordered ascending, but in the first case, I'd expect a similar result to the one in this last example.

Hi Valenti,

Ordering data gathered from number of nodes can be slower on ndbcluster then ordering locally available data on innodb in many cases. So if you are reporting that order by can be slower on ndbcluster then on innodb then yes, that is true and is expected behavior. It will not be so always and in all cases but often will be.

On the other hand if you are reporting that ORDER BY DESC is slower then ORDER BY ASC, can you show us the result of the same query with different ORDER BY? I see you shown the DESC vs ASC for InnoDB but I don't see the same query for ndbcluster. Also can you show the EXPLAIN EXTENDED as neither this DESC slowness I can reproduce with any of the current 7.3 and 7.4 releases.

kind regards
Bogdan Kecman

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".