efficient query help

Hi all,

I have crafted the following query which returns the results I want but
runs extremely slow (I'm sure it's the "not in" part that does it) since
the tables can contain large amounts of data.

Is there a better, more efficient way to build this query?

It's important that I return zero counts for id flag combinations that do not
have an entry in the log table hence the union with the "not in":

id is a text field and flgtime is a timestamp. flags is a table that contains
a single column with a row for each unique flag (text).

select id, flag, count(*) as count
from log
where date(flgtime) = '2002-04-16'
and flag is not null
group by id, flag
union
select distinct l.id, f.flag, 0 as count
from log l, flags f
where (l.id, f.flag) not in
(select id, flag
from log
where date(flgtime) = '2002-04-16'
and fag is not null)
group by l.id, f.flag
;

Thanks for any suggestions.

--
Laurette Cisneros
Database Roadie
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
Where's my....bus?