Re: A few questions about index usage

[Satheesh Bandaram]

Harri Pesonen wrote: It seems that there is quite a lot to do in optimizations and elsewhere in Derby. I'm still impressed about it, I like Derby much more than HSQL. It would be great to get into developing Derby, but of course the learning curse must be steep. Since you mentioned you had other database development experience in the past, I think, your learning curve (I am assuming it was a typo in your message... when you said 'curse' :-) ) should not be steep. Given compact code base of Derby and generally very good design, it is not very difficult to get involved... Satheesh -- Harri Harri Pesonen wrote: > Thanks for you comments and for creating a JIRA item. I think that the > first case is a separate thing, because the following is slow as well: > > SELECT MIN(Id), MIN(Id) + 1 FROM Customer > > This case could be optimized to do only one MIN-traversal. I think > that this is a more fundamental thing, perhaps requiring more thought, > and some kind of global optimizations. This goes into another optimization that Derby could use... Common _expression_ evaluation, which would evaluate common parts of expressions only once. Yet another enhancement request? :-) For your original problem with min() and max() in one query, you may be able to use the following query as a work-around. Select min(myid), max(myid) from (select min(id) from Customer union all select max(id) from Customer) myTab(myid); Something like this could also be used for this new query... making subquery evaluate the min() once and outer query could select both min() and min()+1 output. Instead of Derby recognizing this common _expression_ evaluation, you are doing it by hand. Yes, I agree, Derby should be able to do this... reasonably easily. Satheesh