The two queries have a very big difference:
----- query 1
SELECT COUNT(*)
FROM customers
WHERE ID > 10000
AND country = 'US' ;
----- query 2
SELECT *
FROM customers
WHERE ID > 10000
AND country = 'US' ;
While the second query returns all rows that match the WHERE
conditions, the first one has an aggregate function (COUNT()
) in the SELECT
list, so it does an aggregation, a collapsing of rows that match the conditions into one row and returns only one number, the number of rows that match the conditions.
So, for the first query, there is no sensible reason to have an ORDER BY
. The result is one row only. Even more, it should produce an error as the rows (that have been collapsed into one) may have different values in the country
and created_at
columns. So, which one should be used for the ordering (say in a case where you had a GROUP BY
and the result set was more than one rows)?
You can test at SQL-Fiddle that SQL-Server, when you add ORDER BY country, created_at
, it produces the error:
Column "customers.country" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
An error is produced in Postgres, too.
But even in MySQL that may allow such non-standard syntax, to add ORDER BY
in the first query, the optimizer is smart enough to not take that into account for the execution plan. There is nothing to order. One row will be returned anyway. You can check that by viewing the execution plans with EXPLAIN
. Simple test at SQL-Fiddle: Mysql-test
Oracle (version 11g2) seems to allow such nonsense too. You can see the execution plan here: Oracle-test. Not sure how the plan should be interpreted but it seems that Oracle at least knows that it's one row only so the "sorting" operation is not costly.
You didn't provide CREATE TABLE
scripts; thus, it's hard to give you a specific advice; I'll try to describe general approach....
If you want to speed up this query, first of all, make sure you have indexes on the columns that define join condition, used as a filter (WHERE
), or column[s] you have in GROUP BY
or ORDER BY
. In general, a good sign that some indexes are missed is when EXPLAIN
shows "NULL" in key,key_len
and "ALL" in type
column (not always though; for instance if you select all rows from the table, or table is small enough, so full scan is faster than index seek + lookup). Then you may want to tweak some indexes to make them covering for this query.
Side note. The query seems to me like a BI query which normally is not executed against OLTP database. When dealing with a big data, it makes sense to build a few cubes based on data from operational db and query them instead of original data. The nature of OLTP implies high level of normalization and optimization for INSERT/UPDATE/DELETE, not for SELECTs (still possible, but queries can be very long, not clear, and quite slow).
Best Answer
You can use a case expression like:
You can also use a
filter
clause for the count aggregates:For the third attribute you can either reuse the aggregates:
or add another level of nesting: