having a table with 2 indexed numeric columns, i need to find all the records like this:
select count(*) from tbl where field1 in (5,10) and field2 in (3,7,12); this is terribly slow. also if written with OR syntax: where (field1=5 or field1=10) ... years ago this was programmed to compute the product of the combinations and execute a number of separate queries: select count(*) from tbl where field1 = 5 and field2 =3; select count(*) from tbl where field1 = 5 and field2 =7; ... this way the indexes are used that the queries are fast. but too many. i have now rewritten them to a single query using subqueries to avoid the many db roundtrips: select ( (select count(*) from tbl where field1 = 5 and field2 =3) + (select count(*) from tbl where field1 = 5 and field2 =7) + ... ); measuring shows a 10% speedup compared to separate queries. is this the best i can do with h2database? using 1.3.166, also tried latest stable 1.3.176. beta versions of h2 are not an option. is there documentation about this? all i find is how fast h2 performs in the good cases, but no information about where it's lacking. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
