Is there really somebody that uses DBMail with SQLite??? Does this make
sense?
Am 06.03.15 um 07:48 schrieb Thomas Raschbacher:
Am 05.03.2015 um 17:28 schrieb Jure Pečar:
On Wed, 4 Mar 2015 13:21:04 +0100
Jure Pečar <pega...@nerv.eu.org> 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
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
_______________________________________________
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
