Help me please. Derby does not use index with order by
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