Hello, Probably the best place to rebuild the header cache (because you added more headers you're interested in or whatever other reason) would be with dbmail-maintenance. The message injectors would keep it up to date for new messages, of course.
One thing you need to make sure you handle is when there are multiple headers of the same name in the message - the most obvious example is Received-by, but others certainly show up, too (eg. Delivered-To, X-Spam-*, etc.). You could either not add the UNIQUE(message_id, header_type) and allow multiple entries, or even concatenate all the headers together so you get a single header_cache entry for all the headers of that type, and of course have to parse them out on the client (which I guess you do anyways, to unfold whitespace). jn ---- Original Message ---- From: Magnus Sundberg <dbmail@dbmail.org> To: dbmail@dbmail.org Subject: Re: [Dbmail] 2.0 and header-cache Sent: Fri, 05 Dec 2003 19:11:57 +0100 > Mark Mackay - Orcon wrote: > > >>Suppose we stored the headers in a separate table and used the > >>database indexing facilities. > >>We could then do without a separate header cache, by just using > >>well formulated queries. > > > > > > I did that previously, and the speed hit was quite profound each time > > someone did a search. I ended up writing a perl-script to batch-process and > > generate my own table cache every five minutes and when someone logged into > > webmail. Worked better, but still was icky. > > > Well, I believed you could be helped by a fulltext index but I > might be wrong. > 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? > - 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. > > The trick is just, > How do you automagically rebuild the header table? > This may also be a source for DOS attacks but, if you are able to > get this kind of access to the mail system, you will for shure be > able to DOS it some way or another. > > /Magnus > > > > _______________________________________________ > Dbmail mailing list > Dbmail@dbmail.org > https://mailman.fastxs.nl/mailman/listinfo/dbmail > -- End Original Message -- -- Jesse Norell jesse (at) kci.net
