På fredag 02. mai 2014 kl. 02:17:58, skrev Craig James <cjames(at)emolecules(dot)com
<mailto:cjames(at)emolecules(dot)com>>: On Thu, May 1, 2014 at 4:26 AM, Andreas Joseph
Krogh<andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com>> wrote: I have a schema
where I have lots of messages and some users who might have read some of them.
When a message is read by a user I create an entry i a table message_property
holding the property (is_read) for that user. The schema is as follows: [...]
create table person( id serial primary key,
username varchar not null unique
); create table message(
id serial primary key,
subject varchar
); create table message_property(
message_id integer not null references message(id),
person_id integer not null references person(id),
is_read boolean not null default false,
unique(message_id, person_id)
); [...] So, for person 1 there are 10 unread messages, out of a total
1mill. 5 of those unread does not have an entry in message_property and 5 have
an entry and is_read set to FALSE. Here's a possible enhancement: add two
columns, an indexed timestamp to the message table, and a "timestamp of the
oldest message this user has NOT read" on the person table. If most users read
messages in a timely fashion, this would (in most cases) narrow down the
portion of the messages table to a tiny fraction of the total -- just those
messages newer than the oldest message this user has not read.
When you sign up a new user, you can set his timestamp to the time the
account was created, since presumably messages before that time don't apply.
Whether this will help depends a lot on actual use patterns, i.e. do users
typically read all messages or do they leave a bunch of unread messages sitting
around forever? Thanks fort the suggestion. A user must be able to read
arbitrary old messages, and messages don't expire. -- Andreas Jospeh Krogh
CTO / Partner - Visena AS Mobile: +47 909 56 963 andreas(at)visena(dot)com
<mailto:andreas(at)visena(dot)com> www.visena.com <https://www.visena.com>
<https://www.visena.com>