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<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<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 h2-database+unsubscribe@**
>> googlegroups.com <h2-database%[email protected]>.
>> For more options, visit this group at http://groups.google.com/**
>> group/h2-database?hl=en<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 h2-database+unsubscribe@**
> googlegroups.com <h2-database%[email protected]>.
> For more options, visit this group at http://groups.google.com/**
> group/h2-database?hl=en <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.
