From derby-user-return-12970-apmail-db-derby-user-archive=db.apache.org@db.apache.org Mon Jul 12 21:43:18 2010 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 44817 invoked from network); 12 Jul 2010 21:43:18 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 12 Jul 2010 21:43:18 -0000 Received: (qmail 43833 invoked by uid 500); 12 Jul 2010 21:43:18 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 43788 invoked by uid 500); 12 Jul 2010 21:43:17 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 43781 invoked by uid 99); 12 Jul 2010 21:43:17 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 12 Jul 2010 21:43:17 +0000 X-ASF-Spam-Status: No, hits=-0.7 required=10.0 tests=RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of rbrewer@lava.net designates 64.65.64.27 as permitted sender) Received: from [64.65.64.27] (HELO outgoing03.lava.net) (64.65.64.27) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 12 Jul 2010 21:43:09 +0000 Received: from naushika.ics.hawaii.edu (naushika.ics.hawaii.edu [128.171.10.159]) by outgoing03.lava.net (Postfix) with ESMTPSA id DFCAE101E5 for ; Mon, 12 Jul 2010 11:42:15 -1000 (HST) Date: Mon, 12 Jul 2010 11:42:14 -1000 From: Robert Brewer To: Derby Discussion Subject: Re: Unexpected slow query performance Message-ID: <2CD5236A45B814881053E691@naushika.ics.hawaii.edu> In-Reply-To: References: <746E475302F9EDC7838E29E5@naushika.ics.hawaii.edu> <4C37ED96.208@gmail.com> X-Mailer: Mulberry/4.0.8 (Mac OS X) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Checked: Checked by ClamAV on apache.org --On July 11, 2010 12:15:04 AM +0200 Knut Anders Hatlen wrote: > Bryan Pendleton 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!