On Wed, 2003-07-02 at 11:44, Jesse Norell wrote: > With upcoming schema changes (cached headers, etc.), it'd > probably be worth stepping back and looking at the best way to > handle message storage, and see what improvements can be made. > Even something as simple as a messageblk flag as to whether the > block contains headers or the body could fix the above issue. > While on the subject of storing messages, I don't know that > filesystem files are suited to dbmail as it complicates database > replication, which is certainly a desired feature.
Where is the schmea changes are being discussed? I haven't seen any mention of it anywhere, I would like to be part of the discussion. A quick design: perhaps something like the following. messages: One row per message similar to the current messages table except without all the flags. message_flags: Keeps all the flags (seen, answered deleted). id int4 primary key flag_id int2 (references a list of possible flags in another table) flag_value int2 (value of flag). the advantage to this type of setup is that you use zero space for flags that are equal to the default value. Also, when a flag changes it only updates a very small row, now a larger row that has several flags. Also it allows the definition of additional flags without changing the database schemea. message_headers: similar to the current message_blks table except it only keeps the portion of the message that contains headers. This fixed the problem mentioned of a large set of headers and also easily flags the message block that contains all the header information message_body: contains the remainder of the message, could be broken up like the current message_blks table or not, I would prefer not, but I don't know if mysql is up to it. Thoughts?
