db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Harri Pesonen <fue...@nic.fi>
Subject A few questions about index usage
Date Thu, 26 Jan 2006 18:35:27 GMT
I have been testing Derby for a few days. I noticed a couple of odd 
things. Table definition is

create table Customer (
    Id bigint generated by default as identity primary key,
    Name varchar(128),
    Modified timestamp default current_timestamp

There are no extra indexes, but Id gets automatically an index. I have 
inserted a million rows.

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?

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 

I am wondering if Derby is being improved in this area, and if I could 
help. I have developed database systems in the past.

Thanks, Harri

View raw message