query: select TERMID, STRING from terms_index where STRING like 'aa%'; num records in table: 13 million num records that match: 1.3 million execution time: unknown, didn't return in 15 minutes so i stopped it. h2 version 1.3.163 (2011-12-30) connection params: none. i tried higher cache sizes like CACHE_SIZE=65536 and more, but run into "GC overhead limit exceeded" exceptions. in mysql the same query takes 0.009s to execute and 15 secs to return all records.
some more example queries and numbers: - term 'ba%' takes 11 seconds and returns 51k records. (mysql: 11 secs) - term 'ge%' takes 14 seconds and returns 25k records. (mysql: 8 secs) some queries return after minutes. table definition: COLUMN_NAME DATA_TYPE PK NULLABLE DEFAULT AUTOINCREMENT REMARKS JDBC Type SCALE/SIZE PRECISION POSITION ID INTEGER YES NO (NEXT VALUE FOR PUBLIC.SYSTEM_SEQUENCE_5A111EBC_A409_4852_B98C_CF9BD85965A4) YES 4 10 0 1 TERMID INTEGER NO NO NO 4 10 0 2 STRING VARCHAR(60) NO NO NO 12 60 0 3 DIRECTION CHAR(8) NO NO NO 1 8 0 4 TRANSFORMATIONS ARRAY NO NO NO 2003 0 0 5 indexes: INDEX_NAME UNIQUE PK DEFINITION TYPE TERMS_INDEX_STRING NO NO STRING ASC NORMAL PRIMARY_KEY_1 YES YES ID ASC NORMAL example of explain: PLAN SELECT\n TERMID,\n STRING,\nFROM PUBLIC.TERMS_INDEX\n /* PUBLIC.TERMS_INDEX_STRING: STRING >= 'ax'\n AND STRING < 'ay'\n */\nWHERE (STRING LIKE 'ax%') -- 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.
