H2's optimizer is very local. It doesn't do anything fancy like join flattening.
On Friday, 19 April 2013, Brian Craft wrote: > 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] <javascript:_e({}, > 'cvml', 'h2-database%[email protected]');>. > To post to this group, send email to > [email protected]<javascript:_e({}, 'cvml', > '[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. > > > -- 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.
