scott wrote:
> When I call COUNT(*) FROM (SELECT X ... GROUP BY X) on my test data set 
> (about 100k eventID rows) it runs in 0.25s. That's a bit slow, but hmm ok.
> 
> If I change the SQL statement to COUNT(*) FROM (SELECT DISTINCT X ... ) 
> it takes 11.27s.  Huh?

I'm guessing when you do a SELECT DISTINCT, it's internally creating a 
temporary table.

> Also, with the DISTINCT method, as I repeatedly execute the query the 
> database size seems to have grown from 7MB to 14MB then to 31MB... HUH?  

Do you have an option to compact the database again and see if it shrinks? 
Did you perchance close the connection to the database between queries so it 
frees up temporary tables?  (I'm just guessing here.)

Do these questions not even make sense for the compact edition? :-)

-- 
   Darren New, San Diego CA, USA (PST)
   My fortune cookie said, "You will soon be
   unable to read this, even at arm's length."

Post a reply to this message