Hi there, Consider these statements:
drop table test if exists; create table test (id int primary key, field1 int, field2 int); create hash index idx_field1 on test(field1); create hash index idx_field2 on test(field2); /* 1 - uses idx_field1 */ explain select count(*) from test where field1=1; /* 2 - uses idx_field2 */ explain select count(*) from test where field2=1; /* 3 - uses idx_field1 */ explain select count(*) from test where field1=1 and field2=1; /* 4 - uses idx_field1 */ explain select count(*) from test where field2=1 and field1=1; Why is it that lines 3 and 4 both use idx_field1 instead of idx_field2 for an index? How does H2 go about choosing an index in this situation? Are there any tricks I can use to gently persuade H2 to use idx_field2 in either lines 3 or 4? Regards, Steve -- 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.
