In some email I received from "Matthew T. O'Connor" <matthew@zeut.net> on Fri, 4 Jul 2003 03:15:34 -0400, wrote:
> From: "Aaron Stone" <[EMAIL PROTECTED]> > > The reason for a new headers table is not so that the headers are taken > > out of the messageblks table, but rather because we want some of those > > headers to be pulled out and stored in columns of their own. > > > > The new table would have just a messageid and a few columns named after > > the most often used headers. I would expect these all to be indexed, btw. > > The actual header itself *should* be stored verbatim and intact as a > > chunk of the message, but a "fastheaders" table that holds From, To, > > Subject, Date and few others will dramatically speed up many simple IMAP > > listing operations as well as your most common searches. > > Again, I don't think we should have columns specific to particular headers, > I think it would be better to have a more generic structure for the > message_headers table like: > id serial primary key > header text (possibly an int referencing a lookup table of headers) > header_value text (contains the value of the header) the database wont get bulky? let say avarage 15 headers per message (my case) and 10000 messages. 15x10000 and here we go we have 150 000 records + same size doubled indeces .. imagine more. obviously it's not a problem of a query to access message_blks and header_blks at the same time when a message have to be retrieved, at the same time imagine the complexity of this query :) Also too many indces aint good.. this structure is _so_ granulated. It looks nice, Lets discuss more things and actually get them researched. the optimal solution should be somewhere between: performance + genericness + granulation, also dont think how to optimize dbmail but the database too, this game has been played by two players. Anyone else? > Index both of these columns and it will be quite fast. By default we would > probably only add typical columns such as To, From, Subject, Date etc... but > this allows things to be added as needed. If a particular site / user / > mailbox / folder needs to search on a non default header (size for example) > then the server can be configured to track this information without needing > to change database structure. header_common header_more but the most commonly used headers (by dbmail) in common and all the others inside header_more, less records less indeces. cheers -
