« Return to Thread: Help me please. Derby does not use index with order by

Help me please. Derby does not use index with order by

by lihuig :: Rate this Message:

Reply to Author | View in Thread

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

 « Return to Thread: Help me please. Derby does not use index with order by