Extremely slow subqueries when subquerying on strings
-----------------------------------------------------
Key: DERBY-4422
URL: https://issues.apache.org/jira/browse/DERBY-4422
Project: Derby
Issue Type: Bug
Affects Versions: 10.5.3.0
Environment: Suse Unix 10.3.
Reporter: Tony Brusseau
SELECT DISTINCT C.INTERNAL_ID
FROM OC_CONCEPTS C
WHERE
(C.INTERNAL_ID IN (SELECT DISTINCT OCS.CONCEPT_ID FROM OC_CONCEPT_STRINGS
OCS WHERE OCS.CONCEPT_ID=1))
Both queries above are trivial and they are both acting on fully indexed
primary key columns. This query runs just fine.
However, when I make the small modification of searching on an indexed text
column in the sub query, it takes over 6 seconds to run, even though both
queries independently take < 1ms run.
SELECT DISTINCT C.INTERNAL_ID
FROM OC_CONCEPTS C
WHERE
(C.INTERNAL_ID IN (SELECT DISTINCT OCS.CONCEPT_ID FROM OC_CONCEPT_STRINGS
OCS WHERE OCS.NL_LC = 'dash'))
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
