mysql order by -id vs order by id desc

Question

I wish to fetch the last 10 rows from the table of 1 M rows.

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `updated_date` datetime NOT NULL,
  PRIMARY KEY (`id`)
)

One way of doing this is -

select * from test order by -id limit 10;

**10 rows in set (0.14 sec)**

Another way of doing this is -

select * from test order by id desc limit 10;

**10 rows in set (0.00 sec)**

So I did an 'EXPLAIN' on these queries -

Here is the result for the query where I use 'order by desc'

EXPLAIN select * from test order by id desc limit 10;

And here is the result for the query where I use 'order by -id'

EXPLAIN select * from test order by -id limit 10;

I thought this would be same but is seems there are differences in the execution plan.

Eal

Did you run both of the query on same session?? some time this could happens just because of database cash. – Camm

@JakeGould yes tht's right – Camm

Answer 1

RDBMS use heuristics to calculate the execution plan, they cannot always determine the semantic equivalence of two statements as it is a too difficult problem (in terms of theoretical and practical complexity).

So MySQL is not able to use the index, as you do not have an index on "-id", that is a custom function applied to the field "id". Seems trivial, but the RDBMSs must minimize the amount of time needed to compute the plans, so they get stuck with simple problems.

When an optimization cannot be found for a query (i.e. using an index) the system fall back to the implementation that works in any case: a scan of the full table.

Doley

Answer 2

As you can see in Explain results,

1 : order by id
MySQL is using indexing on id. So it need to iterate only 10 rows as it is already indexed. And also in this case MySQL don't need to use filesort algorithm as it is already indexed.

2 : order by -id
MySQL is not using indexing on id. So it needs to iterate all the rows.( e.g. 455952) to get your expected results. In this case MySQL needs to use filesort algorithm as id is not indexed. So it will obviously take more time :)

Nodular

Answer 3

You use ORDER BY with an expression that includes terms other than the key column name:

SELECT * FROM t1 ORDER BY ABS(key);

SELECT * FROM t1 ORDER BY -key;

You index only a prefix of a column named in the ORDER BY clause. In this case, the index cannot be used to fully resolve the sort order. For example, if you have a CHAR(20) column, but index only the first 10 bytes, the index cannot distinguish values past the 10th byte and a filesort will be needed.

The type of table index used does not store rows in order. For example, this is true for a HASH index in a MEMORY table.

Please follow this link: http://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html

Shuffle