db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <de...@segel.com>
Subject RE: Help me please. Derby does not use index with order by
Date Fri, 03 Jul 2009 12:34:47 GMT
 

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@gmail.com [mailto:lihuig@gmail.com] 
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


Mime
View raw message