db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jeffrey Lichtman <swa...@rcn.com>
Subject Re: A few questions about index usage
Date Thu, 26 Jan 2006 19:53:07 GMT

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


Mime
View raw message