On Thu, 2007-08-23 at 12:26 +0200, Paul J Stevens wrote:
>
> > I have loads of these in my slow query log and the box is suffering.
> >
> > # Query_time: 75 Lock_time: 0 Rows_sent: 0 Rows_examined: 225432
> > SET timestamp=1187861236;
> > SELECT message_idnr FROM dbmail_messages m JOIN dbmail_physmessage p
> ON
> > m.physmessage_id=p.id JOIN dbmail_headervalue v ON
> v.physmessage_id=p.id
> > JOIN dbmail_headername n ON v.headername_id=n.id WHERE
> m.mailbox_idnr=2
> > AND n.headername IN ('resent-message-id','message-id') AND
> > v.headervalue='<[EMAIL PROTECTED]>'AND
> > p.internal_date > NOW() - INTERVAL 3 DAY;
> >
> > What client makes these and why etc?
>
> This query comes from the suppress_duplicates code.
Could this not be handled with a unique index? Can an index include the
date check function? If so, just make a table with the headervalue,
mailbox_idnr and date, and a unique index across it .. try inserting the
value of either message-id or resent-message-id into it and if it's
already there the insert will fail (of course it could fail for other
reasons too..). That ought to be much quicker than a 75 second SELECT.
(Or maybe an index could help with the existing SELECT?)
--
Jesse Norell
Kentec Communications, Inc.
[EMAIL PROTECTED]
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
