Paul J Stevens wrote:
> Jonathan Feally wrote:
>
>> What is the command line you/your client is issuing? I do not think that
>> it is a sort, as message-id is not one of the fields for sort that can
>> be used.
>>
>
> Still, the setup in dbmail-mailbox.c,_append_join_headervalue is broken
> imo. That query should be reduced to something simpler.
I'm not sure how we could make it any shorter other than some shorter
table names in a single query. If we looked up the headername_id first
for the matching header, then we could omit joining it in the larger
query. I don't think it will be any faster though. We still have to look
at the 4-5 tables either way.
In the IMAP SORT, we are trying to get all the message_idnr's in a
mailbox sorted by the criteria. 100% of the message_idnr's need to be
returned.
This leaves us with
SELECT message_idnr FROM dbmail_messages m
We can omit joining dbmail_physmessage if we don't need the size or
internal_date.
JOIN dbmail_phymessage p ON p.id=m.phymessageid
Now we need to know the headername_id for the header to be
sorted/searched on
JOIN dbmail_headername hnxxx ON hnxxx.headername=lower('XxX')
Now we have to attach our headervalue pointer to each message row -
if no matching header, then we need to left join so we get nulls.
LEFT JOIN dbmail_header hxxx ON m.phymessage_id=hxxx.physmessage_id
Attach the actual headervalue to our message row - we don't really
need to left join as we should have a row that matches our pointer but
just in case
LEFT JOIN dbmail_headervalue hvxxx ON hxxx.headervalue_id=hvxxx.id
Now we specify what mailbox and message status is to be included
WHERE mailbox_idnr=? AND status IN (0,1);
For SEARCH - now we just add some more conditions to the WHERE clause.
When adding additional headers for sorting or searching, each row from
the dbmail_headername, dbmail_header, and dbmail_headervalue need to be
joined in using a different table short name - thus the appending of the
header we want to the h, hn, and hv shortnames.
-Jon
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
_______________________________________________
Dbmail-dev mailing list
Dbmail-dev@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
