db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bryan Pendleton <bpendleton.de...@gmail.com>
Subject Re: Unexpected slow query performance
Date Sat, 10 Jul 2010 03:48:38 GMT
> SELECT * FROM SensorData WHERE Source = ? ORDER BY Tstamp DESC FETCH FIRST ROW ONLY
>
> The table has over 3 million rows, and the select statement above is
> taking 10-12 seconds to execute. Naively, I would expect to be able to
> find the row with most recent Tstamp fairly quickly since it is indexed.

It's probably not using the index on Tstamp descending because the WHERE
clause mentions the Source column. You can figure out exactly what Derby
is doing by capturing and reading the query plans:
http://wiki.apache.org/db-derby/PerformanceDiagnosisTips

You can sometimes force Derby to use the index that you want it to use
by special controls called optimizer overrides:
http://db.apache.org/derby/docs/10.6/tuning/ctunoptimzoverride.html

thanks,

bryan

Mime
View raw message