db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Robert Brewer <rbre...@lava.net>
Subject Re: Unexpected slow query performance
Date Mon, 12 Jul 2010 21:42:14 GMT
--On July 11, 2010 12:15:04 AM +0200 Knut Anders Hatlen 
<knut.hatlen@oracle.com> wrote:

> 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

Thanks to everyone who responded. Knut's suggestion worked like a charm, 
the queries are now running about 25 times faster and well within my 
requirements!

Thanks again to the Derby community!

Mime
View raw message