Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

Let's say we have a table called "customers" with these columns ("id", "name", "country", "created_at).

There's an index by ("country", "created_at").

If 

SELECT COUNT(*) 
FROM customers 
WHERE ID > 10000 
AND country = "US" 
ORDER BY country, created_at

is executed, does the Query Optimizer select the same path as if no COUNT were requested, as in

SELECT * FROM ...//same conditions

?

Rephrasing, does MySQL Query Optimizer take into account sort orders when resolving SELECT COUNT?

share|improve this question
1  
ORDER BY is processed after SELECT. So, when the query reaches the ORDER BY, the result set is one row only. – ypercube Jun 14 '12 at 0:50
And in standard SQL, you can't use anything in the ORDER BY that is not in the result set. Some DBMS allow it - especially if there is no grouping. So the grouping into one row that COUNT(*) forces, should make the query to produce an error, something like ORDER BY country: an expression that appears in ORDER BY is not declared in SELECT list. – ypercube Jun 14 '12 at 0:55
But how does MySQL know whether to do a table scan or to use an index without taking the ORDER BY expression into account? I'm almost convinced that the EXPLAIN result both for SELECT * and SELECT COUNT(*) is the same even if there's an ORDER BY sentence. – martincho Jun 14 '12 at 1:34
1  
@martincho: why don't you have a look at the EXPLAIN output then? – a_horse_with_no_name Jun 14 '12 at 7:40

1 Answer

up vote 3 down vote

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.

share|improve this answer
1  
This makes more sense. I'll remove mine. +1 !!! – RolandoMySQLDBA Jun 14 '12 at 20:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.