chiperortiz chiperortiz - 1 year ago 63
MySQL Question

MYSQL has orderBy on count(*) some impact on perfomance?

I have a simple query which count the number of records is a plain Query with some inner Joins and some criterions.

something like this.

select count(*)
from ......
where ....
order by most 4 fields.

My question the order by asc or desc at most 4 fields has some impact on the performance? or is just ignore or optimized by the engine.

Sorry if the question is plain or simple best regards.

Answer Source

First, I should note that your query, as written, will return exactly one row. You have an aggregation function with no GROUP BY. In this situation, the ORDER BY is basically a no-op (I don't know if MySQL goes through the motions for one row or not).

In general, the performance impact of order by depends on the number of rows, not the number of keys.

I can only think of two occasions when an order by has minimal impact on performance:

  • An index can be used for the ordering.
  • It follows a GROUP BY and uses the aggregation keys (this is only true in MySQL which does a sort for the GROUP BY).

And, of course, an ORDER BY on few rows (such as 4 rows) would be pretty negligible performance-wise.

The impact, though, has much less to do with the size of the keys than with the number of rows and the overall size of the rows. With multiple joins and a WHERE clause, it is unlikely (but not impossible) that your query could use an index for the ORDER BY.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download