Re: IN clause and query plans

[Mike Matrigali]

Work is actively happening in the development line to address issues
with use of IN.  You may want to monitor the discussion in jira 
DERBY-47.  Hopefully 10.3 will address your issues.

https://issues.apache.org/jira/browse/DERBY-47


Dan Karp wrote:
I'm seeing some slowness in a query that should be using an index, and
I'm wondering if it's because Derby's query optimizer hates my use of
the IN clause.  I'm using 10.2.2 embedded.

Here's the query and plan with the 78-element IN clause:

Executing prepared statement: SELECT parent_id, id, unread, flags,
tags FROM mboxgroup1.mail_item WHERE mailbox_id = ? AND parent_id IN 
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?) ORDER BY parent_id


Apologies for following up to my own post, but it appears that the 2.5-second 
execution time for this query is dwarfed by the 8-second prepare time.  Is that 
amount of time to prepare a statement that uses an index, uh, normal?