On websites that have product counts by their categories, For example, newegg:
...
Discount Item (23)
New Product (13)
...

Do they (sql) COUNT those products on the fly when the page loads?

Or do they keep a table of product counts to display without calculating the value each time the page is loaded?

The latter is faster, but keeping the count table accurate after updates/deletions sounds more difficult.

6/13/2007 1:13:02 PM

cache

6/13/2007 1:28:37 PM

And you can put triggers on inserts and deletes that will update the counts for you.

6/13/2007 1:52:26 PM

Probably both.

Have your webpage ask your database for a count(). If you find that it takes an unacceptably long time (it won't take a long time), add an index. If that doesn't speed things up enough, then worry about taking care of that specific case with code.

The easiest code to maintain is the code you don't write.

"We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil."

6/14/2007 10:52:08 AM