I'm trying to understand the cause of a table scan when doing a select
through a join table for a many-to-many relationship. Tables are "probes"
and "scores". The join table is called "join" (perhaps should be renamed).
Written as follows, it appears to be using the indexes:
explain SELECT * FROM `probes` LEFT JOIN `joins` ON `joins`.`pid` =
`probes`.`id` LEFT JOIN `scores` ON `scores`.`id` = `sid` WHERE
`probes`.`exp` = 4
SELECT
PROBES.ID,
PROBES.EXP,
PROBES.NAME,
JOINS.PID,
JOINS.I,
JOINS.SID,
SCORES.ID,
SCORES.EXPSCORES
FROM PUBLIC.PROBES
/* PUBLIC.PROBE_NAME: EXP = 4 */
/* WHERE PROBES.EXP = 4
*/
LEFT OUTER JOIN PUBLIC.JOINS
/* PUBLIC.INDEX_PID: PID = PROBES.ID */
ON JOINS.PID = PROBES.ID
LEFT OUTER JOIN PUBLIC.SCORES
/* PUBLIC.PRIMARY_KEY_9: ID = SID */
ON SCORES.ID = SID
WHERE PROBES.EXP = 4
But the library I'm using put parentheses around the first join:
explain SELECT * FROM (`probes` LEFT JOIN `joins` ON `joins`.`pid` =
`probes`.`id`) LEFT JOIN `scores` ON `scores`.`id` = `sid` WHERE
`probes`.`exp` = 4
SELECT
PROBES.ID,
PROBES.EXP,
PROBES.NAME,
JOINS.PID,
JOINS.I,
JOINS.SID,
SCORES.ID,
SCORES.EXPSCORES
FROM (
PUBLIC.PROBES
/* PUBLIC.PROBE_NAME: EXP = 4 */
LEFT OUTER JOIN PUBLIC.JOINS
/* PUBLIC.INDEX_PID: PID = PROBES.ID */
ON JOINS.PID = PROBES.ID
)
LEFT OUTER JOIN PUBLIC.SCORES
/* PUBLIC.SCORES.tableScan */
ON SCORES.ID = SID
WHERE PROBES.EXP = 4
Now there's a table scan.
What's the difference between these two queries?
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
