I think Magnus puts it quite succinctly here:
Magnus Sundberg wrote:
> Matthew T. O'Connor wrote:
>
>>
>> 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)
>>
>> 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.
>
>
> I believe we should look at typical imap/pop queries that the typical
> imap/pop clients do, you know outlook, outlook express, mozilla, pine, etc.
>
> When we know the typical queries, can we optimize the database structure.
>
> This is something you can and should hard code into your software.
>
> The case of a particular site / user ... is probably so rare that you
> should allow a little bit extra load on the database for these queries.
>
> From the mailinglist have I seen that the IMAP copy command required a
> change in the next generation database layout for improved performance.
>
> /Magnus
>
Database optimization is needed. Shall we start gathering stats and
logging queries to further such an effort, or is the new version going
to change everything anyway?
Magnus Sundberg wrote:
> Matthew T. O'Connor wrote:
>
>>
>> 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)
>>
>> 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.
>
>
> I believe we should look at typical imap/pop queries that the typical
> imap/pop clients do, you know outlook, outlook express, mozilla, pine, etc.
>
> When we know the typical queries, can we optimize the database structure.
>
> This is something you can and should hard code into your software.
>
> The case of a particular site / user ... is probably so rare that you
> should allow a little bit extra load on the database for these queries.
>
> From the mailinglist have I seen that the IMAP copy command required a
> change in the next generation database layout for improved performance.
>
> /Magnus
>
Database optimization is needed. Shall we start gathering stats and
logging queries to further such an effort, or is the new version going
to change everything anyway?