Mysql Question posted in
The official MySQL documentation can be found here.

what's the difference between "order by created_time desc" and "order by -created_time" in mysql

Normally, I would take them as the same.
why different performance ?

BY using mysql:5.7

For a table holds 10 million rows

— order by created_time desc

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE points_full_table range created_time_index created_time_index 5 4986750 100.00 Using index condition

— order by -created_time

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE points_full_table ALL created_time_index 9973500 50.00 Using where; Using filesort 
-- TAKE 0.018S
select SQL_NO_CACHE * from sample.points_full_table where created_time > '2023-01-01' order by created_time desc limit 1000;

-- TAKE 5.323S
select SQL_NO_CACHE * from sample.points_full_table where created_time > '2023-01-01' order by -created_time limit 1000;
Tags:

2

Answers


  1. 0

    Order by created_time desc will return latest inserted data first in descending order where other will work inverse and return records in ascending order

    Login or Signup to reply.
  2. 0

    The performance difference occurs because of how MySQL handles sorting.

    With ORDER BY created_time DESC, MySQL retrieves rows using the index in ascending order and then performs a filesort to reorder them in descending order, which is slower and processes all 9.97 million rows. This takes 5.323 seconds.

    In contrast, ORDER BY -created_time scans the index in reverse order directly, avoiding the filesort and processing fewer rows (4.98 million), which is much faster, taking only 0.018 seconds.

    This significant time difference is due to the efficient index scan and the avoidance of sorting overhead.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search