2 Simple tables:
CREATE TABLE IF NOT EXISTS EVENT (ID INT AUTO_INCREMENT PRIMARY KEY,
COMPONENT VARCHAR(1024) NOT NULL, CODE INT NOT NULL, STATEMENT
VARCHAR(1024) NOT NULL, LEVEL INT NOT NULL, "TIMESTAMP" TIMESTAMP NOT
NULL);
CREATE TABLE IF NOT EXISTS EVENTPARAMETER (ID INT AUTO_INCREMENT
PRIMARY KEY, NAME VARCHAR(128) NOT NULL, VALUE VARCHAR(1024) NOT NULL,
EVENT_ID INT NOT NULL, FOREIGN KEY(EVENT_ID) REFERENCES EVENT(ID) ON
DELETE CASCADE ON UPDATE CASCADE)
Index on the EVENT."TIMESTAMP" column:
CREATE INDEX IF NOT EXISTS EVENT_TIMESTAMP ON EVENT ("TIMESTAMP")
The EVENT table has 3.5 million rows in it.
Performing the following simple query results on a table scan on
EVENT:
SELECT E.ID, E.CODE, E.COMPONENT, E.LEVEL, E.STATEMENT, E."TIMESTAMP"
FROM EVENT E LEFT JOIN EVENTPARAMETER EP ON E.ID=EP.EVENT_ID ORDER BY
E."TIMESTAMP" DESC LIMIT 50 OFFSET 0
However, if I add a new index (note the DESC on the column):
CREATE INDEX IF NOT EXISTS EVENT_TIMESTAMP_DESC ON EVENT ("TIMESTAMP"
DESC)
the EVENT_TIMESTAMP_DESC index is used instead of a table scan.
Is H2 not able to use an index in the reverse order at this time?
Thanks,
Chris
--
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.
