MySQL group by count(1) with having by

Question

I am brand and new with my current job i have see the following SQL sentence.

SELECT t.name store,p.id, p.name,count(1)
FROM regular_shippings sh
JOIN
   products p ON matching columns.
ANOTHER JOINS
WHERE criteria
AND anotherCriteria
GROUP BY p.id
HAVING count(1) > 0
ORDER BY t.name,count(1) desc;

My first impression is counting the records in

the select

also in the

having by

also in the

order by

Impacting performance for me is better use a alias. But is this assertion correct? or is not is doing the job only once? or the engine is smart enough to recognize it and just replace the previous values of the count(1) in the latter calls?

What i mean this query would perform better?

SELECT t.name store,p.id, p.name,count(1)c
FROM regular_shippings sh
JOIN
   products p ON matching columns.
ANOTHER JOINS
WHERE criteria
AND anotherCriteria
GROUP BY p.id
HAVING c>0
ORDER BY t.name,c desc;

Sorry if the question is plain!

The use of an alias would have little to no impact on the query. The expensive parts of the query would typically be the group by and order by and reading the data. — Gordon Linoff
– Gordon Linoff, Commented Sep 12, 2018 at 11:15

Thorsten Kettner · Accepted Answer · 2018-09-12 11:20:40Z

1

COUNT(1) is a result from the aggregation (GROUP BY). It is used in

the SELECT clause to be displayed,
in the HAVING clause to limit results and
in the ORDER BY clause for sorting.

It's not three different counts taking place. So it doesn't matter whether you are giving it an alias or not.

(On a sidenote: Most DBMS don't even allow the alias name given in the SELECT clause to be used in HAVING, as HAVING is supposed to occur before SELECT.)

answered Sep 12, 2018 at 11:20

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

chiperortiz Over a year ago

it's not counting the records on the 3 stages?? it's counting only once?

Gordon Linoff Over a year ago

@ThorstenKettner . . . I am guessing that MySQL would actually do the count three times. However, the overhead is minimal. Do you have a reference specifying that MySQL would do such expression elimination?

chiperortiz Over a year ago

i think that MySql will do the count 3 times.

Thorsten Kettner Over a year ago

So you think that MySQL checks which results it needs after aggregation, and this is t.name and p.id and p.name and count(1) and count(1) and count(1)? That looks quite unlikely. But I must admit that I don't know how MySQL works internally, so my answer is no more than a sophisticated guess it seems. I always took it for granted that database systems look at what they actually need to select. Hence: @Gordon Linoff: No, I don't.

Collectives™ on Stack Overflow

MySQL group by count(1) with having by

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related