On Wed, 2005-03-09 at 16:54 +0100, Tom Ivar Helbekkmo wrote: > Geo Carncross <[EMAIL PROTECTED]> writes: > > > 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; > > That made *no* sense. What are you trying to achieve?
I was _trying_ to get separate execution plans that didn't touch the individual records (when sqlite can get all the answers from the index, it does). I ended up discovering that select count(*), count(case when seen_flag > 0 Then 1 else null end), count(case when recent_flag > 0 then 1 else null end) FROM dbmail_messages WHERE status < '2' and mailbox_idnr='1'; was even faster, and with the following index: CREATE INDEX dbmail_messages_8 ON dbmail_messages (mailbox_idnr,status,recent_flag,seen_flag); didn't have to touch any records in sqlite. -- Internet Connection High Quality Web Hosting http://www.internetconnection.net/
