On Wed, 2005-03-09 at 15:42 +0000, Aaron Stone wrote: > Were those queries intended to produce the same results? NB: I didn't add > the indexes you suggest.
You really should. No, I used UNION so that separate query plans would be generated. To make sure results are always given one should probably have a: 'q',COUNT(*) as the request for each. This was a minor note. The result would then be three separate rows, second column being the interesting part. Obviously, dbmail would have to calculate row1-row0 manually, but I suspect that won't be a problem. > 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) Mysql is terrible to test with this because AFAIK, there's no way to get mysql to tell you the execution plan. PostgreSQL and SQLite however, _can_ and I examined the SQLite execution plan to make certain it doesn't turn this query into a sequential scan across the database (whereas the original _does_) Since you can't index aggregates, I selected COUNT(*) which is handled specially by SQLite (and probably PG as well) > 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); > > > .. > > _______________________________________________ > Dbmail-dev mailing list > Dbmail-dev@dbmail.org > http://twister.fastxs.net/mailman/listinfo/dbmail-dev -- Internet Connection High Quality Web Hosting http://www.internetconnection.net/
