If I want to count the number of a multivalued attribute, which way is faster:
COUNT(attribute) on a whole table
b) adding an extra attribute which counts these stored values (to be accessed via
a is much more likely to return the correct value. Option
b requires a new table and implementing code to maintain it. This would definitely be a sign of premature optimization. I would expect you want a query something like.
SELECT attribute, COUNT(attribute) FROM mytable GROUP BY attribute
If the attribute column is indexed, it is possible for this query to be satisfied by reading the index and never accessing the table. Whether this is done, or the table read would depend on the database optimizer.
On some databases, you may be able to get a good estimate of the counts from the optimizers metadata. This generally works best with few values for the attribute, and relatively frequent regeneration of the optimizer data.
one thing you got to keep in mind is: if you materialize the count, you might have some fun on the concurrency side. however, a materialized column is of course always a lot faster.