db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <knut.hat...@oracle.com>
Subject Re: Unexpected slow query performance
Date Sat, 10 Jul 2010 22:15:04 GMT
Bryan Pendleton <bpendleton.derby@gmail.com> writes:

>> 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.

And if that's the problem, creating a compound index on Source and
Tstamp should improve the performance:

CREATE INDEX SourceTstampDesc ON SensorData(Source, Tstamp DESC);

Derby's inability to take advantage of the two non-compound indexes on
Source and Tstamp (desc) is logged in JIRA:
https://issues.apache.org/jira/browse/DERBY-1257

-- 
Knut Anders

Mime
View raw message