chiperortiz - 2 months ago 13

MySQL Question

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 .........at most 4 fields.

My question the order by

Sorry if the question is plain or simple best regards.

Answer

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`

.