Re: counting rows

From: Volker Hetzer <volker.hetzer_at_ieee.org>
Date: Tue, 04 Apr 2006 09:00:16 +0200
Message-ID: <LMudnYJtlJDghK_ZRVnyiQ_at_giganews.com>

bucaliman_at_gmail.com wrote:
> It was
> much more an issue of right db designing than anything else (although
> db designing is tied to performance). I think I will store the count,
> keep things in their places,
My sense of "right design" tells me, information shouldn't be twice in the database. That includes row counts. That kind of "caching" is definitely not part of the schema design phase.

> (what would be searching without
> sorting?). Despite the need of increment/decrement, the count will be
> immediately available in the object's result set.
I don't know what you mean about immediately, since everything takes time, but if this is about query complexity, use a view: create my_user_table_view

	as
		select attribute_1,...,attribute_n,count(*) picture_count
			from users,pictures
			where users.userid=pictures.userid
			group by attribute_*
	;

Then, always use this view instead of my_user_table. If you run into a performance problem (for instance because a frequent query which for some reason can't take a few seconds is "select * from my_user_table_view" without any where clauses), use a materialized, indexed or cube view, depending on your database system.

For me a general heuristics is to design the tables and relations as clean as possible, then use views for the regular queries, nesting them so as to keep the structure simple for the guy who has to maintain it when you are on vacation.
Then, if there are performance problems, materialize/index/cube the offending views.

Lots of Greetings!
Volker Received on Tue Apr 04 2006 - 09:00:16 CEST