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 h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
