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 <abrancate...@schema31.it> 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 > DBmail@dbmail.org > http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
signature.asc
Description: Message signed with OpenPGP using GPGMail
_______________________________________________ DBmail mailing list DBmail@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
