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
