Hacker News Reader

Comment by unnouinceput | Hacker News Reader

Whenever I see "select count(star_sign)" I shake my head. The first optimization you learn as a newby dabbling in SQL is to always use "select count(some_fucking_id)" and not "star_sign". Who wrote this query should be fired, because it has 2 of this problem, one in a subquery!

View on HN

That's harsh, could you explain why, please?

As far as I understand (and I'm the author) `COUNT(*)` means "count the number of rows, regardless of the values". `COUNT(id)` means "count all non-NULL id's" which ends up being the same for non-nullable columns, but requires additional processing for nullable ones. Thus the first option should be able to optimize out more processing time in some situations and at worst be the same.

This is confirmed by some quick googling, it's also confirmed by me running the query with a COUNT(*) and COUNT(id) - both run in the same amount of time, and it's also how it works in a SQL engine[0] I've implemented - COUNT(*) is able to optimize out the most underlying data loading.

[0]:https://github.com/cube2222/octosql

Yeah, if you have the ID's as null. But why you would do such thing in the first place? Have the ID be "serial primary key" and you'll never have null values in there ever!

Btw, that's the second optimization you learn. So you telling me that in your case, you have null values in your ID's? You should be fired trice then.

Since you're hell bent on doing tests, do one with a dozen millions rows, just ID and a varchar as columns, and in one case have the ID have nulls, and in the other case be like I said. Then see the difference when you run "select count(ID) where the_varchar_column = 'Steve'" vs "select(star_sign) bla bla" and come back to me. Image would be appreciated. Then go rewrite your DB structure and the query when you realize the performance

I'm not sure what value your harsh tone adds to your comment. Sharing experiences like this publicly benefits everyone. It obviously exposes blindspots in one's knowledge and it is expected that noone knows everything. That is the point of the. Comments functionality in HN. So that we can all share things that we know and absorb things we don't.

I personally enjoyed this article and learnt a few things from it. I learnt a few things from your comment too but it would have been better without all the harsh words.

I've never said I have null values, I've just used it to explain what performance optimization opportunities COUNT(*) has vs COUNT(id).

It would be great if you'd explain why what I've written is not the case or provided some citation for that.

> [0]:https://github.com/cube2222/octosql

nice tool, thanks for making this available.

if i had to guess COUNT(*) may load everything per row datawise vs using a index. but I'd have to run tests to confirm =)

I do think the parent thinks that. But it's simply not true based on my research. COUNT(*) doesn't need any actual data from the rows, so I'm wondering if there's something else.

First result in Google confirms that at least for Postgres: https://www.citusdata.com/blog/2016/10/12/count-performance/

Another one from stackoverflow: https://stackoverflow.com/questions/2710621/count-vs-count1-... with a citation:

> Don't be fooled by those advising that when using * in COUNT, it fetches entire row from your table, saying that * is slow. The * on SELECT COUNT(*) and SELECT * has no bearing to each other, they are entirely different thing, they just share a common token, i.e. *.

yeah I didn't really believe it, just didn't feel like doing the effort of researching it because in reality its never come up in practice for me. which hinted at it not being a real problem anymore.

Reply on news.ycombinator.com