Re: Message body search

Andrea Brancatelli <[email protected]> Mon, 15 Sep 2014 12:20:42 +0200
Newsgroups	gmane.mail.imap.dbmail
Message-ID	<[email protected]>
I just realized that the “data” column in dbmail_mimeparts is a “blob”, thus cannot have a full text index on it.

On a “test” machine I tried to convert it to long text but seems that there are some extended chars as well… after all it’s a blob, not a text.

I also realized that, obviously, running a full text index on that would index all the mime attachments, and that’s obviously nonsense, as is nonsense to actually do a LIKE on the mime attachments….

Thus I’d like to suggest my 2 cents…. would be hard to implement an indexed column in dbmail_mimeparts with the mime-type for the part? Thus even by touching almost nothing else in the code we could change the “SEARCH” query to search only on text (text, text/html, whatever) parts, not on everything?

Thanks.
-------
Andrea Brancatelli
Il giorno 14/set/2014, alle ore 13:23, Andrea Brancatelli <[email protected]> ha scritto:

> Hello everybody.
> 
> On our setup (MySQL) any type of search on the full body of the message doesn't work because it times out, and that's pretty obvious since it has to scan about 400GB of InnoDB table with no index on it.
> 
> But time has changed and now (from MySQL 5.6 on) even InnoDB has Full Text Search support. Is there any possibility to switch the "full body" search to a FTS-like fashion query instead of a "like"?
> 
> Currently searching for "gcloud" anywhere in the message from RoundCube generates this query:
> 
> SELECT DISTINCT m.message_idnr FROM dbmail_mimeparts k LEFT JOIN dbmail_partlists l ON k.id=l.part_id LEFT JOIN dbmail_physmessage p ON l.physmessage_id=p.id LEFT JOIN dbmail_header h ON h.physmessage_id=p.id LEFT JOIN dbmail_headervalue v ON h.headervalue_id=v.id LEFT JOIN dbmail_messages m ON m.physmessage_id=p.id WHERE m.mailbox_idnr = 1 AND m.status IN (0,1)  AND (v.headervalue LIKE '%gcloud%' OR k.data LIKE BINARY '%gcloud%') ORDER BY m.message_idnr
> 
> That takes almost 40 seconds to run, enough for RoundCube to timeout (or for the user to get bored).
> 
> Thanks
> 
> P.S.
> 
> Peter: I wrote you a few private mails about consulting, did you get them?
> 
> -- 
> Andrea Brancatelli
> Schema31 S.p.a.
> Responsabile IT
> 
> ROMA - FIRENZE - PALERMO 
> ITALY
> Tel: +39. 06.98.358.472
> Cell: +39 331.2488468
> Fax: +39. 055.71.880.466
> Società del Gruppo SC31 ITALIA
> _______________________________________________
> DBmail mailing list
> [email protected]
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

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