Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
Message-Id: <6.2.3.4.2.20060126114819.039d6ca0@pop.rcn.com>
Date: Thu, 26 Jan 2006 11:53:07 -0800
To: <derby-dev@db.apache.org>
From: Jeffrey Lichtman <swazoo@rcn.com>
Subject: Re: A few questions about index usage
In-Reply-To: <43D9166F.1050504@nic.fi>
References: <43D9166F.1050504@nic.fi>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed


>1) "SELECT MIN(Id) FROM Customer" and "SELECT MAX(Id) FROM Customer" 
>are both fast, but "SELECT MIN(Id), MAX(Id) FROM Customer" is slow, 
>taking 5 seconds. Why?

Derby knows how to use an index to quickly find a minimum or a 
maximum (by traversing down one side of the B-tree or the other). It 
doesn't know how to do both in the same query, which would take two traversals.

>2) "SELECT * FROM Customer ORDER BY Id" is fast but "SELECT * FROM 
>Customer ORDER BY Id DESC" is slow. Why? Can't it scroll the index backwards?

Nope - Derby doesn't do backwards scrolling. I think the physical 
data layout could support it (i.e. there are backwards pointers), but 
backwards scrolling has never been implemented. Could someone more 
familiar with the store confirm this (Mike?).


                        -        Jeff Lichtman
                                 swazoo@rcn.com
                                 Check out Swazoo Koolak's Web Jukebox at
                                 http://swazoo.com/