Is there some heuristic that is used to decide whether to do a table scan
for an IN clause?
In the following case, I suspect the index is being used, though I have
trouble understanding the EXPLAIN output.
The IN is in the last WHERE clause:
SELECT
PROBES.NAME,
SCORES.EXPSCORES AS EXPSCORES,
JOINS.I AS I,
PROBES.NAME AS GENE
FROM PUBLIC.PROBES
/* PUBLIC.EID_NAME_INDEX_8: EID = ?1
AND NAME = ?2
*/
/* WHERE (PROBES.EID = ?1)
AND (PROBES.NAME = ?2)
*/
LEFT OUTER JOIN PUBLIC.JOINS
/* PUBLIC.INDEX_PID: PID = PROBES.ID */
ON PROBES.ID = PID
LEFT OUTER JOIN PUBLIC.SCORES
/* PUBLIC.PRIMARY_KEY_91: ID = SID */
ON SID = SCORES.ID
WHERE (I IN(?3, ?4, ?5, ?6, ?7, ?8))
AND ((PROBES.EID = ?1)
AND (PROBES.NAME = ?2))
JOINS is a (poorly named) many-to-many table between PROBES and SCORES,
with columns PID (ID for PROBES), SID (ID for SCORES), and I (which orders
the SCORES rows).
There is an index INDEX_PID on (JOINS.PID, JOINS.I), which is referenced in
the first join, above. But there's no EXPLAIN notation around the IN clause
showing a table scan or index, so I'm not sure if it's benefitting from the
index.
On Monday, October 7, 2013 7:47:40 AM UTC-7, Noel Grandin wrote:
>
>
> On 2013-10-07 16:39, Brian Craft wrote:
> > Regarding the normal range of an IN query, what other way would you
> > write a query that retrieves hundreds of rows by their keys?
>
> That's a good point, there really isn't any other way.
>
> I don't normally do that kind of thing, so that's not normally a problem
> for me :-)
>
> But I know from mucking around in our IN query code that we normally
> generate a table scan for that kind of thing.
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
