Matthew T. O'Connor wrote:
On Tue, 2003-07-08 at 06:44, Magnus Sundberg wrote:
Hi again,
I have done some more thinking about the database structure.
I am not sure of the performance gain with a fast header table
structure.
This would be ideal with a single table and one VARCHAR column
for each _interesting_ header. How do we know what the clients
think are interesting headers?
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.
We could also add something to dbmail, so that dbmail keeps
statistics about which headers that are requested.
I actually beleive that these statistics could be collected and
then just stored into another table in the database, once an hour
or so, just to get it nonvolatile.
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.
/Magnus
