On Wed, 4 Mar 2015 13:21:04 +0100 Jure Pečar <pega...@nerv.eu.org> wrote:
> 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 My conclusions about this query are: * dbmail_mimeparts.data is type longblob * ORDER BY requires temp table * which could go to memory table, but it does not support blob/text types, therefore it goes to disk * LIKE begins with %, therefore it cannot use indexes * which means full table scan * on disk :D Also LIKE BINARY is many times (3+) slower then LIKE. The only difference between the two is that one is case sensitive while the other is not. Things we could investigate: * changing dbmail_mimeparts.data type to something that memory engine supports * like / like binary ... do we need case sensitivity? * mysql 5.6 full text search? * external indexing? Paul? -- Jure Pečar http://jure.pecar.org _______________________________________________ DBmail mailing list DBmail@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
