* vs Naming Field (weedpacket)

Shawazi

On a previous threat Weedpacket said:

The DBMS can optimise for COUNT(*) in ways that it can't for COUNT(<field>) - it doesn't need to look at any fields, for example (and so uses fewer resources).

If my table has a field named id that is primary and auto incrementing in MySQL, it is still better to use COUNT() instead of COUNT(id)? Do you also believe this is true when using SELECT (SELECT instead of SELECT id, name)?

bradgrafelman

Shawazi wrote:
it is still better to use COUNT(*) instead of COUNT(id)?

AFAIK, yes.

Shawazi wrote:
Do you also believe this is true when using SELECT (SELECT * instead of SELECT id, name)?

A big emphatic no. Here, you're talking about SELECT'ing a subset of data vs. all the data in a row only to discard the bits you don't use. It's sort of like a boss asking a secretary to transport two large filing cabinets full of information up 100 flights of stairs so that he can look at the information on two cards.

Shawazi

So I'm sticking to selecting the specific fields. I thought the suggestion was odd. Although I still like the 1984 quote as your sig. Weedpacket.

bradgrafelman

Shawazi wrote:
I thought the suggestion was odd

I'm confused... which suggestion did you think was odd?

MarkR

My answer:

It probably doesn't matter. If you really think it does, benchmark, benchmark, benchmark!
"SELECT COUNT(*)" is clearer to me, and seems more maintainable (if you suddenly rename the column, it will continue to work)
Weedpacket is usually right, but don't ever assume anyone is (especially me 🙂 )

Mark

Shawazi

I think COUNT() is faster but SELECT is slower judging from what both of these guys said.

bradgrafelman

Right, because those are two totally different statements. "SELECT COUNT(*)" means "Tell me how many rows are in the table (using optimizations)."

"SELECT *" means "Send me data from every single column in every single row.... basically everything that's in the table."

Sxooter

A few points.

1: count(*) is very expensive in some databases, such as postgresql. An approximation is often good enough, hence look into using statistics or something if you're running on pgsql.

2: How one database optimizes is not how another might do it. In fact, in mysql, how one table handler optimizes might be different from one to another.

3: Pagination can get very expensive if you're using limit / offset syntax as you get nearer to the end of the data set. Consider partitioning your data by id / date stamp whatever. MySQL doesn't support invisible under the hood partitioning, but rolling your own isn't that hard.

4: count(id) is NOT the same as count() in one big way. count() will count all rows, even ones that are nothing but nulls. count(id) will only count the ids that aren't null. normally this semantic difference doesn't matter. Just about the time you forget about this difference, it will. 🙂