Re: Total column or dynamic count

[Rick James]

"If it ain't broke, don't fix it."

Seriously, It is not obvious which way you should do it..

If you rarely do the COUNT, then COUNT() is more efficient.

If there are a million pictures, then the COUNT() would be noticeably slow. (Real anecdote: On one social web site, a few guys formed a group with the sole goal of uploading a million pictures. Needless to say, this messed with the performance of the site.)

It is "bad practice" to have redundant information in the database (the count in a field, plus being able to compute it).

If you don't have a performance issue now, then why bother changing it?

Just today, I was investigating a simple looking
SELECT id, COUNT(*) FROM tbl
WHERE id IN (... 20 ids...)
GROUP BY id.
It was taking 10 seconds. Why? Because each id occurred between 100K and 400K times in the table. It was "Using index" and fully cached in RAM, yet it still took 10 seconds.

They were looking up the same ids (in different combinations) frequently. I suggested that they cache the counts (for a limited time) in another table. Even if only half the ids are in the cache, that should get the time down to close to 5 seconds. (I contend that is still unacceptable in a web environment.)

OK, sometimes I get long-winded.