MySQL does it matter which column I COUNT() by

Question

I've been wondering about this for quite some time. Is it better to do this where the primary key ticket_id is counted:

SELECT COUNT(ticket_id) 
FROM tickets
WHERE ticket_country_id = 238

Or to do this:

SELECT COUNT(ticket_country_id) 
FROM tickets
WHERE ticket_country_id = 238

In this case ticket_country_id is an indexed foreign key, but we could also assume it's just a non-indexed column (perhaps the answer would be different for non-indexed columns)

In other words, does it matter that I am calling on another column for the COUNT()?

Obviously the performance saving would probably be small, but I like to do things the best way.

No It does not matter. it will always give you same count. it counts even if record is NULL — Priyanshu
– Priyanshu, Commented Feb 26, 2016 at 8:47
@Priyanshu I'm more referring to performance issues. The speed of query execution. — Amy Neville
– Amy Neville, Commented Feb 26, 2016 at 8:50
@AmyNeville, why don't you want to use classical: count(*) or count(1) ? — MaxU - stand with Ukraine
– MaxU - stand with Ukraine, Commented Feb 26, 2016 at 8:52
@AmyNeville, no, you don't need "a column of 1's" - count() and count() are the same thing, unless you use MyISAM where count(*) is bit more optimized . You may want to read this: dev.mysql.com/doc/refman/5.7/en/… — MaxU - stand with Ukraine
– MaxU - stand with Ukraine, Commented Feb 26, 2016 at 8:58
Amy, what i know is count ( * ) and count(column), gives different result when there is Null records. Count(*) returns all rows with No-Null or Null values, where count(column) will return number of rows having no-null values . You ca read a comparison of performance here , percona.com/blog/2007/04/10/count-vs-countcol — Kiran Krishnan
– Kiran Krishnan, Commented Feb 26, 2016 at 8:59

LoztInSpace · Accepted Answer · 2016-02-26 09:34:31Z

2

Yes,it can matter. Select count(*) allows the DB to use whatever resources make sense and are most efficient. It can do as table scan, use a primary key or other index to answer your question.

Count(something-else) means count the non null values. Again, the DB can use several methods such as indexes if such thing are available but you are then asking a different question.

As is often the case with SQL it's better to ask the question you want answers to than play silly games trying to game the system for a few milliseconds here and there.

That helps your future colleagues too by clearly stating the thing you are trying to do.

answered Feb 26, 2016 at 9:34

LoztInSpace

5,6971 gold badge17 silver badges29 bronze badges

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

Collectives™ on Stack Overflow

MySQL does it matter which column I COUNT() by

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related