I have an index on nanotime CREATE INDEX nanotime ON LOGENTRY (nanotime)
Pozdrawiam Bartosz Skorupa 2012/7/11 Ryan How <[email protected]> > I'd put an index on nanotime > > /* scanCount: 2244038 */ indicates it is reading over 2 million records > to find the ones you want! > > > > On 11/07/2012 4:07 PM, Bartosz Skorupa wrote: > > For > > EXPLAIN ANALYZE SELECT logentry.date , logentry.nanotime FROM logentry, > log_categories > WHERE logentry.category = log_categories.id > AND logentry.nanotime >= 10089487311026 > AND logentry.nanotime <= 10090280303361 > ORDER BY 2 ASC NULLS LAST LIMIT 165 OFFSET 0; > > i got > > SELECT > LOGENTRY.DATE, > LOGENTRY.NANOTIME > FROM PUBLIC.LOG_CATEGORIES > /* PUBLIC.LOG_CATEGORIES.tableScan */ > /* scanCount: 9 */ > INNER JOIN PUBLIC.LOGENTRY > /* PUBLIC.CONSTRAINT_INDEX_A6: CATEGORY = LOG_CATEGORIES.ID */ > ON 1=1 > /* scanCount: 2244038 */ > WHERE (LOGENTRY.NANOTIME <= 10090280303361) > AND ((LOGENTRY.NANOTIME >= 10089487311026) > AND (LOGENTRY.CATEGORY = LOG_CATEGORIES.ID)) > ORDER BY 2 NULLS LAST > LIMIT 165 OFFSET 0 > /* > total: 165276 > LOGENTRY.CONSTRAINT_INDEX_A6 read: 10216 (6%) > LOGENTRY.LOGENTRY_DATA read: 155060 (93%) > */ > > and for > > EXPLAIN SELECT logentry.date , logentry.nanotime FROM logentry, > log_categories > WHERE logentry.category = log_categories.id > AND logentry.nanotime >= 10089487311026 > AND logentry.nanotime <= 10090280303361 > ORDER BY 2 ASC NULLS LAST LIMIT 165 OFFSET 0; > > i got > > SELECT > LOGENTRY.DATE, > LOGENTRY.NANOTIME > FROM PUBLIC.LOG_CATEGORIES > /* PUBLIC.LOG_CATEGORIES.tableScan */ > INNER JOIN PUBLIC.LOGENTRY > /* PUBLIC.CONSTRAINT_INDEX_A6: CATEGORY = LOG_CATEGORIES.ID */ > ON 1=1 > WHERE (LOGENTRY.NANOTIME <= 10090280303361) > AND ((LOGENTRY.NANOTIME >= 10089487311026) > AND (LOGENTRY.CATEGORY = LOG_CATEGORIES.ID)) > ORDER BY 2 NULLS LAST > LIMIT 165 OFFSET 0 > > Pozdrawiam > Bartosz Skorupa > > > > 2012/7/11 Ryan How <[email protected]> > >> Do you have an index on nanotime? >> >> If you run EXPLAIN on your query it can tell you if it is using an index >> or table scan. >> >> >> http://www.h2database.com/html/grammar.html?highlight=Explain&search=explain#explain >> >> >> >> >> On 11/07/2012 3:32 PM, Bartosz Skorupa wrote: >> >>> I have a simple DB with table defined : >>> >>> CREATE TABLE "LOGENTRY"( >>> AUTOID BIGINT default '(NEXT VALUE FOR >>> PUBLIC.SYSTEM_SEQUENCE_10A53561_6D15_428A_820B_7EA1722BCB5D)' not null, >>> DATE BIGINT not null, >>> NANOTIME BIGINT not null, >>> MESSAGE CLOB, >>> ID BIGINT not null, >>> LEVEL SMALLINT not null, >>> CATEGORY INTEGER not null, >>> REQUESTERCLASS VARCHAR, >>> REQUESTERMETHOD VARCHAR, >>> LINENUMBER INTEGER, >>> THREADID INTEGER not null, >>> THREADNAME VARCHAR not null, >>> THREADGROUP VARCHAR not null, >>> THREADPRIORITY SMALLINT not null, >>> STACKTRACE CLOB, >>> FOREIGN KEY (CATEGORY) REFERENCES LOG_CATEGORIES(ID)) >>> >>> CREATE TABLE "LOGGINGDB20120711_081524"."PUBLIC"."LOG_CATEGORIES"( >>> ID INTEGER PRIMARY KEY not null, >>> CATEGORY VARCHAR not null) >>> >>> I run a example query >>> >>> SELECT logentry.date , logentry.nanotime FROM logentry, log_categories >>> WHERE logentry.category = log_categories.id >>> AND logentry.nanotime >= 4351362368770 >>> AND logentry.nanotime <= 4352180803886 >>> ORDER BY 2 ASC NULLS LAST LIMIT 165 OFFSET 0; >>> >>> This query run about ~1300ms. >>> >>> The same DB and the same query on Derby run ~3ms, what is wrong with >>> this? >>> >>> -- >>> 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/-/GPd5Wv0qYtcJ. >>> 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. >> >> > -- > 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. > -- 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.
