Re: Comparative performance

Jim C. Nasby wrote:
> Make sure these indexes exist if you'll be updating or inserting into
> entry:
>
> CREATE INDEX topic__subject_id ON topic(subject_id);
> CREATE INDEX topic__actor_id ON topic(actor_id);

Actually, topic's primary key is topic_id.

> Also, the fact that subject and actor both point to topic along with
> subject_type and actor_type make me suspect that your design is
> de-normalized. Of course there's no way to know without more info.

Yes, the design is denormalized.  The reason is that a book or article is
usually by a single author (an "actor" topic) and it will be listed under one
main topic (a "subject" topic).  There's a topic_entry table where additional
actors and subjects can be added.

It's somewhat ironic because I used to teach and/or preach normalization and the
"goodness" of a 3NF+ design (also about having the database do aggregation and
sorting as you mentioned in your other email).

> FWIW, I usually use timestamptz for both created and updated fields.

IIRC 'created' ended up as a DATE because MySQL 4 has a restriction about a
single TIMESTAMP column per table taking the default value of current_timestamp.

Joe