Mysql Order BY Speed

Question

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

Answer 1

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.

Answer 2

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.