On 05/15/2013 04:38 PM, Jure Pečar wrote:
I applied these to our production mysql (and dropped those you recomended in
the attached files) and immediately noticed about 10x drop in Innodb row
operations.
However looking at slow query log I still see some queries pop up often. This
one is the most common:
SELECT message_idnr FROM dbmail_messages m LEFT JOIN dbmail_physmessage p ON
m.physmessage_id=p.id LEFT JOIN dbmail_datefield ON
m.physmessage_id=dbmail_datefield.physmessage_id WHERE m.mailbox_idnr = 3733
AND m.status IN (0,1) ORDER BY sortfield,message_idnr;
These take anywhere from 2 to 15 seconds to complete.
Can you recommend additional indexing statements to speedup these?
Oh, you should have a staging system for such changes to be tested first.
Also I don't see see dbmail_messages.sortfield field in a schema I am
using. Neighter do I see that field in dbmail-3.0.2 src/dbmail-mailbox.c.
What version of dbmail do you use? Can you attach your mysql db schema?
Can you provide the output of:
DESCRIBE SELECT message_idnr FROM dbmail_messages m LEFT JOIN
dbmail_physmessage p ON m.physmessage_id=p.id LEFT JOIN dbmail_datefield
ON m.physmessage_id=dbmail_datefield.physmessage_id WHERE m.mailbox_idnr
= 3733 AND m.status IN (0,1) ORDER BY sortfield,message_idnr;
query?
--
Best regards,
Pavlo Lavrenenko,
PortaOne, Inc., Junior Software Developer
Tel: +1-866-SIP VOIP (+1 866 747 8647) ext. 7624
PortaOne - VoIP Solutions Company
Visit our Website: http://www.portaone.com
_______________________________________________
DBmail mailing list
[email protected]
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
