I think I figured it out. I know it can only use one index per joined
table and it is using the SamplePointId index (I thought it would use
one for the join + one for each table). So I created a
SamplePointId_SampleDateTime index and it is now using that instead and
seems to be faster.
Thanks,
Ryan
Ryan How wrote:
Hi,
I have a query which is running slower than I think it should. It
takes about 6 seconds to complete first time, then 500ms each
subsequent time and returns about 100 rows. The table has about
300,000 rows in it.
I can't figure out how to tell if it is using an index or not (or the
right index) so I was hoping someone could help me. I have an index on
the column SAMPLEDATETIME, so I would have thought the query would run
very quickly and just use that index. The "Explain" command only tells
me about the index used for the join, but doesn't tell me about the
where clause.
EXPLAIN SELECT "T132"."SITEID", "T131"."SAMPLEDATETIME",
"T131"."COLLECTEDBY" FROM "DB"."PUBLIC"."SAMPLE" AS "T131",
"DB"."PUBLIC"."SAMPLEPOINT" AS "T132" WHERE "T131"."SAMPLEPOINTID" =
"T132"."SAMPLEPOINTID" AND "T131"."SAMPLEDATETIME" >= {D '2010-01-01'
} ORDER BY "T131"."SAMPLEDATETIME" DESC, "T131"."COLLECTEDBY" ASC
SELECT T132.SITEID, T131.SAMPLEDATETIME, T131.COLLECTEDBYFROM
PUBLIC.SAMPLEPOINT T132 /* PUBLIC.SAMPLEPOINT_DATA */INNER JOIN
PUBLIC.SAMPLE T131 /*
PUBLIC.SAMPLE_SAMPLEPOINTID_SAMPLEPOINT_SAMPLEPOINTID_INDEX_9:
SAMPLEPOINTID = T132.SAMPLEPOINTID */ ON 1=1WHERE (T131.SAMPLEDATETIME
>= TIMESTAMP '2010-01-01 00:00:00.0') AND (T131.SAMPLEPOINTID =
T132.SAMPLEPOINTID)ORDER BY 2 DESC, 3
Many Thanks,
Ryan
--
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.
