Anyone? Are these two queries equivalent? I suspect the second one merely coerces the joins to be performed in the same order as the optimizer would choose anyway, but that somehow prevents the optimizer from picking an appropriate index. I tested briefly on mysql, and it showed no difference in index usage between these two forms.
Is there any way to force the index selection for the second join? On Wednesday, April 17, 2013 2:17:00 PM UTC-7, Brian Craft wrote: > > 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.
