> A few questions, > - What should the table structure be? > - What about automatic rebuilding the table cache, if you get a > query for a new header?
If you change the syntax, then as Jesse pointed out, dbmail-maintenance would be a logical place. Failing that, based on the lifetime of most email -- you could simply add another header, and for the first month or so the header would only show up for newly delivered email. > - What about this table structure > > CREATE TABLE cached_header_types ( > header_idnr int(??) NOT NULL AUTO_INCREMENT, > header_name text, > PRIMARY KEY (header_idnr), > INDEX (header_name) > ) TYPE=InnoDB; > This table holds the headers types we cache, > > CREATE TABLE header_cache ( > header_cache_idnr bigint(21) NOT NULL AUTO_INCREMENT, > header_type int(??), > header_val text, > message_id bigint(21), > UNIQUE(message_id, header_type), > FOREIGN KEY (`message_id`) > REFERENCES `messages` (`message_idnr`) > ON DELETE CASCADE, > ) TYPE=InnoDB; > > This way, we have a good index on the user/header combination, > This might not be the best approach, > I beleive the typical query is > Get headers A,B and C from mailbox x. > This would reguire something like > SELECT header_type, header_val FROM (header_cache, messages) > WHERE header_cache.message_id=messages.message_idnr AND > messages.mailbox_idnr=<XXX> AND > (header_cache.header_type=head_id1 OR > header_cache.header_type=head_id2 OR ... > header_cache.header_type=head_idN) > > I beleive we would get a quite good index and quite fast replies. I'm not suggesting that a header cache is built up for every header, or for use in IMAP optimisations, etc (although others may want this). The issue I see with the above model is the one-to-many relationship. Typically in a webmail client you want to display a single summary row of info for each message (usually pulling stuff out of the messages table too, like message_idnr, etc. Thus a one-to-one join works well. My complex SQL is a bit rough so maybe there is a way to do this elegantly. A single sql transaction seems the best way for my purposes. "select messages.*, messageheaders.* from messages, messageheaders where messages.message_idnr=messageheaders.message_idnr order by <sorting header> " If someone can point out an easy way to get the same output from the above structure, then fine by me. Guessing the above model has merit for it's easy customization of header types. One other note -- I don't think everyone wants the header cache. A lot of people are using IMAP webmail clients, and unless dbmail itself can benefit from such a cache, I'd suggest it's a compile-time option. No processing overhead if you don't need it, and typically you're gonna choose the list of headers you want once or twice in the lifetime of a dbmail setup. If people want more headers, it should only affect DB size and processing time at delivery/rebuild. Selects from I should still be fast and only the desired headers can be returned. /Mark
