Re: imap search query
Andrea Brancatelli <[email protected]>
| Newsgroups | gmane.mail.imap.dbmail |
|---|---|
| Organization | Schema31 S.p.a. |
| Message-ID | <[email protected]> |
There's already some Oracle-specific code in the codebase, it's, just one query, it could be implemented with a specific code for Full Text capable/not capable DBMS. Il 06/03/15 07:48, Thomas Raschbacher ha scritto: > 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 -- *Andrea Brancatelli Schema31 S.p.A. Responsabile IT* BO - FI - ROMA - PA ITALY Tel: +39.06.98358472 Cell: +39.331.2488468 Fax: +39.055.71880466 Società del gruppo SC31 ITALIA _______________________________________________ DBmail mailing list [email protected] http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail