imap search query

Hello,

I see sudden spikes of load on our dbmail mysql servers. Looking into the slow query logs I usualy find these:

SELECT DISTINCT m.message_idnr FROM dbmail_mimeparts p 
	LEFT JOIN dbmail_partlists l ON p.id=l.part_id 
	LEFT JOIN dbmail_physmessage s ON l.physmessage_id=s.id 
	LEFT JOIN dbmail_messages m ON m.physmessage_id=s.id 
	LEFT JOIN dbmail_mailboxes b ON m.mailbox_idnr = b.mailbox_idnr 
	WHERE b.mailbox_idnr=87 AND m.status IN (0,1)  AND (l.part_key > 1 OR l.is_header=0) AND p.data LIKE BINARY '%Gls%' 
	ORDER BY m.message_idnr

I believe these are IMAP searches.

Mysql processlist show these in "copy to tmp table" state, which kills IO, especially if there's more than one running at the same time. These queries take anywhere up to 20min to complete and rows_examined shows numbers over 350k.

My guess is that users do a search on a large mailbox.

Apart from telling them not to do that, what else can I do to improve performance? Can anything be done at all to improve "like binary" performance apart from setting up a separate indexing server such as solr? Is binary search even necessary? 

DBMail 3.1.10, MySQL 5.5.41

-- 

Jure Pečar
http://jure.pecar.org
_______________________________________________
DBmail mailing list
[email protected]
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail