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
