Am 05.03.2015 um 17:28 schrieb Jure Pečar: > 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? > >
I recon something like supporting mysql 5.6 full text search would cause problems, since it is not the only supported RDBMS. I think paul told me once dbmail doesn'T use stored procedures, because some backend (i think just sqlite) doesn't support it. So if one were to put in some full text capability it would have to work everywhere - and emulate where not ? .. which i guess would be difficult.
signature.asc
Description: OpenPGP digital signature
_______________________________________________ DBmail mailing list DBmail@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
