Mysql Order BY Speed

user_13344325 0
Viewed 907

There are 2 samples.

In the first example, it gives faster results when using orderby. (according to phpmyadmin speed report)

In the other example, I don't use order by, it gives slower results. (according to phpmyadmin speed report)

Isn't it unreasonable that it gives quick results when using Orderby?

The ranking doesn't matter to me, it's the speed that matters.

select bayi,tutar 
from siparisler 
where durum='1' and MONTH(tarih) = MONTH(CURDATE()) and YEAR(tarih) = YEAR(CURRENT_DATE()) 
order by id desc

Speed: 0.0006

select bayi,tutar
from siparisler 
where durum='1' and MONTH(tarih) = MONTH(CURDATE()) and YEAR(tarih) = YEAR(CURRENT_DATE())

Speed: 0.7785

mysql-5.7 sql-order-by query-optimization mysql sql
4 Answers

An order by query will never execute faster than the same query without the order by clause. Sorting rows incurs more work for the database. In the best-case scenario, the sorting becomes a no-op because MySQL fetched the rows in the correct order in the first place: but that just make the two queries equivalent in terms of performance (it does not make the query that sorts faster).

Possibly, the results of the order by were cached already, so MYSQL gives you the result directly from the cache rather than actually executing the query.

If performance is what matters most to you, let me suggest to change the where predicate in order not to use date functions on the tarih column: such construct prevents the database to take advantage of an index (we say the predicate is non-SARGable). Consider:

select bayi, tutar 
from siparisler 
where 
    durum = 1 
    and tarih >= dateformat(current_date, '%Y-%m-01')
    and tarih <  dateformat(current_date, '%Y-%m-01') + interval 1 month
order by id desc

For performance with this query, consider an index on (durum, tarih, id desc, bay, tutar): it should behave as a covering index, that MySQL can use to execute the entire query, without even looking at the actual data.

user_106767160

At 0.0006s, you are almost certainly measuring the performance of the query_cache rather than the execution time. Try both queries again with SELECT SQL_NO_CACHE and see what the performance difference is.

user_18426770

First, I recommend writing the query as:

select bayi, tutar 
from siparisler p
where durum = 1 and -- no quotes assuming this is an integer 
      tarih >= curdate() - interval (1 - day(curdate()) day;

This can take advantage of an index on (durm, tarih).

But that isn't your question. It is possible that the order by could result in a radically different execution plan. This is hypothetical, but the intention is to explain how this might occur.

Let me assume the following:

  • The table only has an index on (id desc, durum, tarih).
  • The where clause matches few rows.
  • The rows are quite wide.

The query without the order by would probably generate an execution plan that is a full table scan. Because the rows are wide, lots of unnecessary data would be read.

The query with the order by could read the data in order and then apply the where conditions. This would be faster than the other version, because only the rows that match the where conditions would be read in.

I cannot guarantee that this is happening. But there are some counterintuitive situations that arise with queries.

user_11440350

You can analyze it through the EXPLAIN command, and then check the value corresponding to the type field, index or all

Example:

EXPLAIN SELECT bayi,tutar 
FROM siparisler 
WHERE durum='1' AND MONTH(tarih) = MONTH(CURDATE()) AND YEAR(tarih) = YEAR(CURRENT_DATE()) 
ORDER BY id DESC;
user_141604590
Related