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 <http://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
<http://LOG_CATEGORIES.ID> */
ON 1=1
/* scanCount: 2244038 */
WHERE (LOGENTRY.NANOTIME <= 10090280303361)
AND ((LOGENTRY.NANOTIME >= 10089487311026)
AND (LOGENTRY.CATEGORY = LOG_CATEGORIES.ID
<http://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 <http://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
<http://LOG_CATEGORIES.ID> */
ON 1=1
WHERE (LOGENTRY.NANOTIME <= 10090280303361)
AND ((LOGENTRY.NANOTIME >= 10089487311026)
AND (LOGENTRY.CATEGORY = LOG_CATEGORIES.ID
<http://LOG_CATEGORIES.ID>))
ORDER BY 2 NULLS LAST
LIMIT 165 OFFSET 0
Pozdrawiam
Bartosz Skorupa
2012/7/11 Ryan How <[email protected] <mailto:[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
<http://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]
<mailto:[email protected]>.
To unsubscribe from this group, send email to
[email protected]
<mailto:h2-database%[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]
<mailto:[email protected]>.
To unsubscribe from this group, send email to
[email protected]
<mailto:h2-database%[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.
