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.

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.

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 :)