db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Karl Wright (JIRA)" <j...@apache.org>
Subject [jira] Created: (DERBY-5142) Optimizer uses table scan when it could use index when multiple OR clauses
Date Thu, 17 Mar 2011 22:18:29 GMT
Optimizer uses table scan when it could use index when multiple OR clauses
--------------------------------------------------------------------------

                 Key: DERBY-5142
                 URL: https://issues.apache.org/jira/browse/DERBY-5142
             Project: Derby
          Issue Type: Bug
            Reporter: Karl Wright


The Derby optimizer doesn't seem to recognize that a query like this:

SELECT id,distance,linktype FROM hopcount WHERE (jobid=? AND linktype=? AND parentidhash=?)
OR (jobid=? AND linktype=? AND parentidhash=?)

... might be best served by using an index declared on hopcount as (jobid,linktype,parentidhash).
 Other databases have no trouble with constructs like this.

This is a very common situation, and blocks Apache ManifoldCF from using Derby as its primary
database choice.

I've verified that the index IS successfully used with the same table statistics when the
query has only ONE clause, e.g.:

SELECT id,distance,linktype FROM hopcount WHERE (jobid=? AND linktype=? AND parentidhash=?)




--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira

Mime
View raw message