Speed up SQL

[Beatrix Willius]

No, didn't work.

Tried something different. I only used "where" instead of "having" and "group":

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, mailbox

where Message.MessageBody REGEX '(?i)osmer' or Message.OriginatorFrom REGEX '(?i)osmer' OR  Message.ReceiverTo REGEX '(?i)osmer' OR Message.MainSubject REGEX '(?i)osmer' OR message.RecID in (select MailID from Attachment where Attachment.AttachmentName REGEX '(?i)osmer')

But this is even slower than before. The speed is acceptable when I take out the embedded SQL for the attachment name. And this query takes about 3 milliseconds!

But when I try the SQL below without embedded SQL then the result is 0 records while the SQL above gives the correct 2.

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, mailbox, attachment 

where Message.MessageBody REGEX '(?i)osmer' or Attachment.AttachmentName REGEX '(?i)osmer' or Message.OriginatorFrom REGEX '(?i)osmer' OR  Message.ReceiverTo REGEX '(?i)osmer' OR Message.MainSubject REGEX '(?i)osmer' 

I need to re-install Access and see what I can do there.

On 16.01.2012, at 09:11, Ruslan Zasukhin wrote:

> May be try add ()  around last ON
> 
> Message INNER JOIN Mailbox ON (Message.MailboxID = Mailbox.RecID  and
> (Message.MessageBody REGEX '(?i)osmer' or Attachment.AttachmentName REGEX
> '(?i)osmer'))

Mit freundlichen Grüßen/Regards

Trixi Willius

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