Re: imap search query

Thomas Raschbacher <[email protected]> Fri, 06 Mar 2015 07:48:15 +0100
Newsgroups	gmane.mail.imap.dbmail
Message-ID	<[email protected]>
Am 05.03.2015 um 17:28 schrieb Jure Pečar:
> On Wed, 4 Mar 2015 13:21:04 +0100
> Jure Pečar <[email protected]> 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.

_______________________________________________
DBmail mailing list
[email protected]
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
signature.asc (application/pgp-signature, 819 B)