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
