LEFT JOIN ON Clause with Multiple Expressions

[pontupo]

Using MySQL version 5.0.45, I have the following problem:

SELECT *
FROM student_completed_section stud
inner JOIN curriculum_unit cur
ON (stud.curriculum_id = cur.curriculum_id
AND stud.unit_id = cur.unit_id);

Shows an explain plan which indicates a simple select on both tables and "ALL" for type and "using where" in extra. The query starts returning results essentially immediately and takes about 2.5 minutes to run it's entirety. This query

SELECT *
FROM student_completed_section stud
left JOIN curriculum_unit cur
ON (stud.curriculum_id = cur.curriculum_id
AND stud.unit_id = cur.unit_id);

shows the exact same explain plan except that the stud table doesn't have a "using where" comment in the Extra section. It, however, never returns results and ultimately has to be canceled. The only difference is the join type. What's somewhat amusing is the the results sets are exactly the same: left join and inner join should return exactly the same thing in this case. Running this:

SELECT *
FROM student_completed_section stud
left JOIN curriculum_unit cur
ON stud.curriculum_id = cur.curriculum_id;

shows the same explain plan as the second query (simple select type, type ALL with no "using where"), but executes within 2 minutes, though this one doesn't return the same result set. This:

SELECT *
FROM student_completed_section AS stud
left JOIN curriculum_unit AS cur
ON stud.curriculum_id = cur.curriculum_id
WHERE stud.unit_id = cur.unit_id;

shows the same explain plan as the first query (simple select type, type ALL with "using where") and executes normally.

The difference between two ON conditions and one seems enormous when it comes to left joins, but makes no difference with inner joins, both as far as the explain plan indicates and the time it takes the query to actually execute. I simply can't explain this and haven't (yet) found anything else to explain it. Really, I can work around this easily enough (as shown by the variety of identical queries, in terms of results, above), but it's soooooo irritating!!!! Thoughts?

 

[Kozusnik]

Are there indexes set on each of the id columns in each table?
stud.curriculum_id,cur.curriculum_id,stud.unit_id, and cur.unit_id

Following the "logic" from above, try running

SELECT *
FROM student_completed_section AS stud,curriculum_unit AS cur
WHERE stud.unit_id = cur.unit_id AND stud.curriculum_id = cur.curriculum_id;

Does that make a difference?

Mark

 

[pontupo]

There aren't any indexes on the columns. This would certainly increase the efficiency of the queries, I just think it's a little odd that the left join approach doesn't work at all.

The query you provide does run, but is really another form of the first one, right? An inner join expressed differently.

I'm thinking more and more that this is just a bug of some kind on this particular version or installation. I can't find a single reference anywhere to this kind of problem.

 

[Kozusnik]

It would be more like an inner join, but you got to get indexed. I index/unique/primary all keys used in joining. It will make a significant improvement. As for the initial question as to why, maybe someone else will have better insight into that.

Mark