Hi, I guess you need a multi-column indes. See also http://www.h2database.com/html/performance.html#storage_and_indexes
Regards, Thomas On Fri, Jul 13, 2012 at 9:40 AM, Noel Grandin <[email protected]> wrote: > Please do an explain plan on the queries and post the results. > > > On 2012-07-13 02:43, Clint Hyde wrote: >> >> here's the situation: >> >> h2 v.166 >> >> I have a DB with about 20 million total rows across all tables. 16 million >> are in one table. >> >> the query: >> >> select taskID, taskname, filename from tasks where state=1 and priority=3; >> >> this is a distributed multi-processing task controller (grid engine) >> program. I need to be aware of the overall state of things, so I need to do >> this query once in a while, for all values of 'priority' >> >> this query is quite expensive, takes 20-24 seconds right now, with 16M >> rows. it's going to get worse. >> >> all the fields in the query are indexed. Why is it so slow? (fwiw, this is >> on a 3GHz 32GB dual-hex-core Mac Pro--blazing fast hardware) >> >> I sure would like this query to run in 1/10 the time (2 sec, and not >> dependent on table size). >> >> ------------------ >> >> related: similar query: >> >> select taskname, count(*) from tasks where state=1 group by taskname >> >> also about 24 seconds >> >> -------------------- >> >> other than this set of queries, H2 is doing good for me, mostly it runs at >> about 5% of cpu or less. >> >> -- clint >> > > > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/h2-database?hl=en. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
