Re: How would you store read/unread topic status?

Greg Stark a écrit :
> All the other comments are accurate, though it does seem like
> something the database ought to be able to handle.
>
> The other thing which hasn't been mentioned is that you have a lot of
> indexes. Updates require maintaining all those indexes. Are all of
> these indexes really necessary? Do you have routine queries which look
> up users based on their flags? Or all all your oltp transactions for
> specific userids in which case you probably just need the index on
> userid.

We are using these indexes, but I can't be sure if we _really_ need them
or not.

I can go into detail. We have:

UserID - TopicID - LastReadAnswerID - WrittenStatus - IsFavorite

So basically, we toggle the boolean flag WrittenStatus when the user has
written in that topic. The same goes for IsFavorite.

We have indexes on them, so we can SELECT every topic WHERE the user has
written. Is it the good way of doing this?

Oh, I've made a mistake before, we have RAID 1 disks, not RAID 0.

>
> You'll probably find 8.3 helps this workload more than any tuning you
> can do in the database though. Especially if you can reduce the number
> of indexes and avoid an index on any flags that are being updated.

I'll start this way, thanks. First 8.3, then I'll check my flags.

I have a lot of ways to investigate and I would like to thank every
contributor here. I might come again with more precise information.

Thanks.