Were those queries intended to produce the same results? NB: I didn't add
the indexes you suggest.
mysql> SELECT COUNT(*) FROM dbmail_messages WHERE mailbox_idnr='5' AND
-> (status='0' OR status='1') UNION
-> SELECT COUNT(*) FROM dbmail_messages WHERE mailbox_idnr='5' AND
-> (status='0' OR status='1') AND seen_flag=1 UNION
-> SELECT COUNT(*) FROM dbmail_messages WHERE mailbox_idnr='5' AND
-> (status='0' OR status='1') AND recent_flag=1;
+----------+
| COUNT(*) |
+----------+
| 1871 |
| 1666 |
+----------+
2 rows in set (0.27 sec)
mysql> SELECT COUNT(message_idnr), COUNT(message_idnr) - SUM(seen_flag),
-> SUM(recent_flag) FROM dbmail_messages WHERE mailbox_idnr = '5'
-> AND status < '2'\G
*************************** 1. row ***************************
COUNT(message_idnr): 1871
COUNT(message_idnr) - SUM(seen_flag): 205
SUM(recent_flag): 1871
1 row in set (0.09 sec)
On Wed, Mar 9, 2005, Geo Carncross <[EMAIL PROTECTED]>
said:
> On Wed, 2005-03-09 at 11:51 +0100, Thomas Mueller wrote:
>> Hi,
>>
>> I get more and more user complaints that my IMAP server is terrible
>> slow, opening a mailbox takes very long.
>>
>> I've analyzed that using PQA (http://pgfoundry.org/projects/pqa/, great
>> tool!), the output is attached if anyone is interested (includes all
>> databases, not only dbmail).
>>
>> The main problem is in db.c, db_getmailbox():
>> SELECT COUNT(message_idnr), COUNT(message_idnr) - SUM(seen_flag),
>> SUM(recent_flag) FROM dbmail_messages WHERE mailbox_idnr = '5' AND
>> status < '2'
>> That query takes up to 15 seconds (!!).
>
> A better query would be:
>
> SELECT COUNT(*) FROM dbmail_messages WHERE mailbox_idnr='5' AND
> (status='0' OR status='1') UNION
> SELECT COUNT(*) FROM dbmail_messages WHERE mailbox_idnr='5' AND
> (status='0' OR status='1') AND seen_flag=1 UNION
> SELECT COUNT(*) FROM dbmail_messages WHERE mailbox_idnr='5' AND
> (status='0' OR status='1') AND recent_flag=1;
>
> Then having the following indexes:
> mailbox_idnr
> mailbox_idnr,status
> mailbox_idnr,status,seen_flag
> mailbox_idnr,status,recent_flag
>
> Then having an index on mailbox_idnr AND status will mean we never have
> to look at the database contents to answer the query (only examining the
> index.
>
> If you feel REALLY wary about using UNION- make union-ability a flag and
> do each query separately if the backend says it can't do UNION.
>
> We should also include the following indexes:
> mailbox_idnr,message_idnr,status
> mailbox_idnr,message_idnr
>
> with SQLite, this plan never touches the records (only the index) - even
> though the plain is much longer (139 ops instead of 57) it's SO MUCH
> faster.
>
> It would take [me] some time to actually test this with PgSQL - and I'll
> do that if someone who has an active dbmail+pg complains.
>
>
> BTW: I added this to my sql/sqlite/create_tables.sqlite for this.
>
> CREATE INDEX dbmail_messages_7 ON dbmail_messages
> (mailbox_idnr,status,seen_flag);
> CREATE INDEX dbmail_messages_8 ON dbmail_messages
> (mailbox_idnr,status,recent_flag);
..
