Re: Performance with large mailboxes and nnsql.el

[Joseph Barillari <jbarilla@princeton.edu>]

[-- Attachment #1: Type: text/plain, Size: 3658 bytes --]

>>>>> "KG" == Kristoffer Gleditsch <toffer@ping.uio.no> writes:
      
      <snip>

    >> If one stores the headers separately, should one also remove
    >> them from the body text?

    KG> One could just split them from the body, storing the entire
    KG> header part of the mail in a separate text record.  That way
    KG> the exact original headers would still be available, while
    KG> being "out of the way" for searches and stuff.

That still doesn't solve the update question. What happens if the user
edits the To: header? Gnus would have to rewrite both the text block
in which the raw headers are stored *and* the entry in the table that
stores the To: headers. This violates the "don't store duplicate data"
axiom of database design, so I'm hesitant to accept it. However, I
don't see any other way to preserve both the original headers and gain
the benefits of indexing them, short of the ugly
replace-headers-with-tags scheme I mentioned in my last message.

<snip>

    KG> I'm not sure about the idea of a generic header table; if
    KG> we're going to split lots of headers into separate tuples, I
    KG> think perhaps one table per header type will be easier to deal
    KG> with.

My inclination is to prefer generality, and use a generalized header
table. This has the advantage of being trivially extensible to cover
different headers, and shouldn't impact lookup speed, if the database
is doing a good job of indexing.

However, it would require (as you indicated in creation2.sql) creating
a unique ID for each header, so as to enable unambiguous references
to individual headers. 

If we used your suggestion and split certain headers into their own
tables, we could add a constraint that forces uniqueness on the
message serial number, without bothering to create a separate serial
number for each header. This applies only for headers that appear once
per message, as multiple-instantiation headers like Received: would
need serial numbers if we split them from the message text.

Perhaps we could have two generalized tables: one for headers that
appear once and only once (Subject:) and one for headers that appear
multiple times (Received:) or can contain multiple items (To:).

    KG> About the mailbox thing: I plan to use the keywords (bad name,
    KG> I'm renaming it to groups) table for that; it contains a
    KG> mapping between groups/mailboxes and messages.  That way the
    KG> same message can belong to several (or no) groups without
    KG> having to store it twice.

Good idea.

    KG> I've thrown together some pseudo-SQL for a new database
    KG> layout, and checked it into CVS as 'creation2.sql'.  It would
    KG> be great if you could take a look at it and let me know what
    KG> you think.

Looks fine. Two issues remain. One: what is the best way to store the
raw headers? Two: Should we use one table, two tables*, or many
tables** to store the split-off headers?

    KG> (Sorry about the late answer.  I'll be pretty busy for another
    KG> month or so, but with some luck I'll get time to look some
    KG> more at this during the summer.  If you want to do stuff with
    KG> this code in the meantime, I think that's great; don't let me
    KG> be stop you.  :-)

The delay is no problem; I've been busy as well. (I just read your
message today.)

--Joe

*One for headers that appear only once per message with a uniqueness
 constraint on the message serial number and the header name, and
 another for headers instantiated more than once, each with its own
 serial number.

**One for each header type.

[-- Attachment #2: Type: application/pgp-signature, Size: 268 bytes --]