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 logwhere 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 fwhere (l.id, f.flag) not in      (select id, flag
fromlog        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?