4 ms·

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".

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 https://github.com/cube2222/octosql

unnouinceput 5y ago
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
- option_greek 5y ago
  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.
- cube2222 5y ago
  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.
  - deleted 5y ago
    [deleted]
sugarkjube 5y ago
> [0]:https://github.com/cube2222/octosql https://github.com/cube2222/octosql nice tool, thanks for making this available.
jatone 5y ago
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 =)
- cube2222 5y ago
  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/ https://www.citusdata.com/blog/2016/10/12/count-performance/ Another one from stackoverflow: https://stackoverflow.com/questions/2710621/count-vs-count1-vs-countpk-which-is-better 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. *.
  - jatone 5y ago
    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.

unnouinceput 5y ago

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

option_greek 5y ago
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.
cube2222 5y ago
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.
- deleted 5y ago
  [deleted]

sugarkjube 5y ago

> [0]:https://github.com/cube2222/octosql https://github.com/cube2222/octosql nice tool, thanks for making this available.

jatone 5y ago

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 =)

cube2222 5y ago
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/ https://www.citusdata.com/blog/2016/10/12/count-performance/ Another one from stackoverflow: https://stackoverflow.com/questions/2710621/count-vs-count1-vs-countpk-which-is-better 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. *.
- jatone 5y ago
  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.