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?
Hi. The problem is in a `ORDER BY' statement. Basically when the results
are ordered by `message_idnr' field only mysql hits the index both in
`WHERE' and `ORDER BY' statements any query works smooth. With complex
`ORDER BY' statements, even if dbmail_headervalue.sortfield would have
an index, query will remain slow.
You can find out more at
http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html :
> In some cases, MySQL cannot use indexes to resolve the ORDER BY,
> although it still uses indexes to find the rows that match the WHERE
> clause. These cases include the following:
>
> You use ORDER BY on different keys:
>
> SELECT * FROM t1 ORDER BY key1, key2;
I would also note that this performance issue will reappear every time
someone sends SEARCH IMAP request with sort parameter or uses
dbmail-export utility (see src/dbmail-mailbox.c::_handle_sort_args()
function). And I don't see how this could be overcomed.
sudo cast Paul
--
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
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
