Hi Thomas, I decided to test again using h2-1.3.159, and based on speed of the query, it seems to be using scans, not indexes. Both IN and OR take ~60 seconds on my 1Ghz machine.
explain SELECT * FROM om WHERE OMATTRIBID IN (26,27) AND objvalue='468203' result: SELECT OM.OMATTRIBID, OM.OBJID, OM.OWNERID, OM.OBJVALUE, OM.LISTORDER, OM.OMID FROM PUBLIC.OM /* PUBLIC.OM_ATTRVAL_ID: OMATTRIBID IN(26, 27) AND OBJVALUE = '468203' */ WHERE (OMATTRIBID IN(26, 27)) AND (OBJVALUE = '468203') explain SELECT * FROM om WHERE (OMATTRIBID=26 OR OMATTRIBID=27) AND objvalue='468203' result: SELECT OM.OMATTRIBID, OM.OBJID, OM.OWNERID, OM.OBJVALUE, OM.LISTORDER, OM.OMID FROM PUBLIC.OM /* PUBLIC.OM_ATTRVAL_ID: OMATTRIBID IN(26, 27) AND OBJVALUE = '468203' */ WHERE (OMATTRIBID IN(26, 27)) AND (OBJVALUE = '468203') Some notes: -The table contains 2M rows. -"OBJVALUE" in this case is a varchar column. -java -Xmx256M -Dh2.queryCacheSize=0 -Dh2.returnLobObjects=false -Dh2.objectCacheSize=50000 -Dh2.pageSize=8192 -Dh2.maxMemoryRows=100000 -cp $H2_HOME org.h2.tools.Server -tcpAllowOthers -tcpPort 9101 -baseDir $H2_DATA -During the query all CPU is user, not wa (swap). -DDL: CREATE CACHED TABLE PUBLIC.OM( OMATTRIBID INTEGER DEFAULT 0 NOT NULL SELECTIVITY 1, OBJID BIGINT SELECTIVITY 5, OWNERID INTEGER SELECTIVITY 1, OBJVALUE VARCHAR(1000000) SELECTIVITY 12, LISTORDER INTEGER SELECTIVITY 1, OMID BIGINT DEFAULT (NEXT VALUE FOR PUBLIC.SYSTEM_SEQUENCE_3F2DECE5_3E35_486B_AFD8_CBDF71B9EDA5) NOT NULL NULL_TO_DEFAULT SEQUENCE PUBLIC.SYSTEM_SEQUENCE_3F2DECE5_3E35_486B_AFD8_CBDF71B9EDA5 SELECTIVITY 100 ); ALTER TABLE PUBLIC.OM ADD CONSTRAINT PUBLIC.CONSTRAINT_9D PRIMARY KEY(OMID); CREATE HASH INDEX PUBLIC.OM_OBJID_OMATTRIBID ON PUBLIC.OM(OBJID, OMATTRIBID); CREATE INDEX PUBLIC.OM_ATTRVAL_ID ON PUBLIC.OM(OMATTRIBID, OBJVALUE); CREATE INDEX PUBLIC.OM_OBJVALUE_IDX ON PUBLIC.OM(OBJVALUE); CREATE HASH INDEX PUBLIC.OM_OBJID_IDX ON PUBLIC.OM(OBJID); -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/3xHYAio1ky8J. 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.
