Casper Langemeijer wrote: > Hi Paul and others! > > I've been very busy making a webmail interface directly into the dbmail > database backend, and that has given me some time to gather some > thoughts on the table layout. > > For a typical email client there are to things that need to be > optimized, which currently are quite slow: > > One is the messagecount (read messages and total) that are displayed for > a dbmail_mailbox. Counting them everytime is quite a strain on the database.
That's why in 2.3+ the dbmail_mailbox rows will have a datetime field (_mtime), so you can reliably cache results. This timestamp field ma be changed into a sequence that is updated after each modification of (messages in) that mailbox to accomodate a RFC for highly concurrent access to shared mailboxes. But this will be solvable. > > Secondly, the data retrieved for creating a message row with from > address. subject, date. message size etc is scattered out along a few > tables. The query needed for this pretty slow. as it contains a fair > number of join's. What I'd like to have is a simple table per > physmessage (or even _in_ dbmail_physmessage) containing the relevant > information. > > Could using stored procedures be a solution to this problem? That join should not be *that* slow. It essentially only involves the dbmail_headervalues and physmessage tables. Also, duplication of cached data is *evil*. Consider using the dbmail_envelope table which contains all the senders, the recipients, date, subject and message-id information. Of course, it's in a IMAP compatible format, so you'll need to parse it. One tip: don't use regexp to do that. Write a tokenizer. Or write a view. -- ________________________________________________________________ Paul Stevens paul at nfg.nl NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31 The Netherlands________________________________http://www.nfg.nl _______________________________________________ DBmail mailing list [email protected] https://mailman.fastxs.nl/mailman/listinfo/dbmail
