If I want to count the number of a multivalued attribute, which way is faster:

a) using COUNT(attribute) on a whole table


b) adding an extra attribute which counts these stored values (to be accessed via SELECT)

  • 2
    That highly depends on what concrete database you're talking about, and on what the table structure and available indexes look like.... – marc_s Nov 17 '14 at 8:58

Option 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,
 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.