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

[lihuig]

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

[Derby-3]

Some parts of this message have been removed. Learn more about Nabble's security policy.

 

Why would it?

 

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

…

 

Lets look at your query. You may be selecting A.updated, but you’re not filtering on that column so why would the database use an index on A.updated?

 

If you want better performance you want an index on cust_id, which would be a secondary index since there’s a possible 1 to many of contracts to customers.

(cust_id appears to be a foreign key.) Do you have indexes on your primary keys A.ID and B.ID?

 

In your second SQL … You’re most likely doing a sequential scan of a single table since you have no filter clauses. So yes, it should be fast. Since the result set is the table, you’re probably using the index in the sort. Your first select statement returns a subset of records based on the filter before you apply the order by clause. So it can’t use the index.

 

Is that what you were looking for?

 

---

From: lihuig@... [mailto:lihuig@...]
Sent: Thursday, July 02, 2009 9:22 PM
To: derby-user @ db. apache. org
Subject: 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