Speed up SQL

[Beatrix Willius]

Hi,

why is the following SQL so very slow? It takes more than 5 seconds in a database with 10.000 records in the message table. What can I do to make it faster? The table structure is Mailbox -> Message -> Attachment.

SELECT Message.MailboxID, Message.RecID, Message.OriginatorFrom as [From], Message.ReceiverTo as [To], Message.MainSubject as [Subject], Message.MainDateTime as [Date], Message.AttachmentCount as [Att.], MailboxName(Mailbox.Mailboxpath) as [Mailbox], lower(Message.OriginatorFrom) as [Order_From], lower(Message.ReceiverTo) as [Order_To], lower(Message.MainSubject)as [Order_Subject]  
FROM Message INNER JOIN Mailbox ON Message.MailboxID = Mailbox.RecID 
GROUP BY Message.MailboxID, Message.RecID, Message.OriginatorFrom, Message.ReceiverTo, Message.MainSubject, Message.MainDateTime, Message.AttachmentCount, Mailbox.MailboxPath 
HAVING RecID in (select RecID from Message where Message.MessageBody REGEX '(?i)osmer') ORDER By [Date] ASC

If I execute only

select RecID from Message where Message.MessageBody REGEX '(?i)osmer'

then the query takes less than 1 second. The same is true for a simple search condition like 

 [Subject] REGEX '(?i)osmer'

instead of the nested sql. I just tried the tune mode, which is very very unhelpful.


Mit freundlichen Grüßen/Regards

Trixi Willius

http://www.mothsoftware.com
Mail Archiver X: The email archiving solution for professionals