db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From lih...@gmail.com
Subject Help me please. Derby does not use index with order by
Date Fri, 03 Jul 2009 02:22:29 GMT
When executing the following sql derby does not use UPDATED COLUMN's index  
and the execution is very very slow.

SELECT A.ID, A.CONTRACTDATE, A.UPDATED, B.NAME FROM CONTRACT A LEFT JOIN  
CUSTOMER B ON A.CUST_ID = B.ID ORDER BY A.UPDATED DESC OFFSET 0 ROWS FETCH  
NEXT 10 ROWS ONLY

CONTRACT table contains about 10,0000 rows and CUSTOMER TABLE contains  
about 2,000 rows. UPDATED COLUMN is a timestamp type and has a desc index.
I check the execution plan and it shows that sorting is external and does  
not use the UPDATED COLUMN index.
But execution of the next sql is very fast:

SELECT A.ID, A.CONTRACTDATE, A.UPDATED, B.NAME FROM CONTRACT A ORDER BY  
A.UPDATED DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Why?

Thanks,

Hui Li

Mime
View raw message