0

This is question is simple i know but i would like to somebody explain me to see if i am getting right.

This simple query does MYSQL always executes the semantics from left to right?

SELECT c03,c04,c05,count(*) as c FROM table 
where status='Active' and c04 is not null and c05 is not null 
group by c03,c04,c05 
having c>1 order by c desc limit 10;

The engine starts from left to right filtering every record by

* Status later comparing c04 and later c05
* Later groups the results
* later filters again the result applying the c>1 filter
* Later sorts and fetch the first 10 results and disposing the others

Or have some other optimizations suppose not index are using...

chiperortiz
  • 4,751
  • 9
  • 45
  • 79

1 Answers1

1

I guess Status later comparing c04 and later c05 depends on the available indexes and the cardinality of the columns, and is not always evaluated in that order. MySQL will try to evaluate the constraint first, which promises to reduce the result set the most at the lowest cost, e.g. constraints using columns without indexes might be evaluated last, because the evaluation is expensive.

The rest leaves little room for other orderings, but some steps may gain benefit from appropriate indexes.

Community
  • 1
  • 1
Ulrich Thomas Gabor
  • 6,584
  • 4
  • 27
  • 41
  • but after of the filters the next steps are this? Later groups the results * later filters again the result applying the c>1 filter * Later sorts and fetch the first 10 results and disposing the others – chiperortiz Apr 04 '17 at 15:10
  • Yes, I think so (this is what my second paragraph refers to). At least I see no possibility to do it in a different order. Filter must come after grouping. To sort makes no sense before filtering, because the complexity to filter is `O(n)` whereas sorting takes `O(n log n)`, so the optimizer should reduce the result set before sorting it. And picking the first 10 rows is only possible after the sorting. – Ulrich Thomas Gabor Apr 05 '17 at 09:28