From: "Magnus Sundberg" <[EMAIL PROTECTED]> > Matthew T. O'Connor wrote: > > This is why I suggested the value pair design, since we won't always > > know what's interesting. > > > Do you have any ideas of how to create an index, to get good > request performance? > something like > CREATE TABLE interesting_headers ( > header_id BIGINT DEFAULT '0' NOT NULL AUTO_INCREMENT, > message_idnr BIGINT DEFAULT '0' NOT NULL, > header_name VARCHAR(32), > header_content VARCHAR(255), > UNIQUE(message_idnr, header_name) > ); > > I beleive in that the interesting headers are defined in the > configuration file. But remember, you need to recreate the fast > header file every time you add another header to be stored in the > fast header table.
one reason to keep the list of "interesting headers" in a table rather than in dbmail.conf is so that the interesting_headers table you defined above would have the header_name be an int that references a primary key in the intereesting_headers_list table. This would reduce the size of the value pair table, and speed queries to it. As for indexes on the table, the one you defined is probably the best for general purpose queries. But it might be better with two separate indexes. The optimizations will be query specific, and probably somewhat database specific (mysql vs. postgresql). > We could also add something to dbmail, so that dbmail keeps > statistics about which headers that are requested. This would certainly be good for a research tool, not sure if it would be usefull in the wild, unless it would automatically update the list of "interesting headers". > > Also, IMAP clients are not all we care about. We also have POP3 (less > > interesting) and direct clients that bypass the IMAP interface, querying > > the database themselves. I know several web apps do this already. > > Also, there is talk extending dbmail to include more exchange type > > functionality and implementing a SOAP interface, thus I think it's a bit > > foolish to optimize the design solely around IMAP. > > I beleive that IMAP is the most important application, with a > well defined standard. > I think the IMAP way of operation is typical for a mail client, I > mean the data flow and the requests that are done between the > server and the client, wether it is SQL, IMAP or POP3. > I think the authors of the direct clients can come up with their > requests for indexes and database structure. Probably true, I just wanted people to remember that IMAP is not everything, but as you said, is probably the most relevant, certainly the largest customer.
