can i make a suggestion?
when you have a WHERE clause like this --
WHERE assignments_questions.assignment_id = 208
then it's a good idea to start off your FROM clause with that table --
ON questions.question_id = assignments_questions.question_id
now, the mysql optimizer is pretty smart, and for inner joins, it's gonna figure out that the fastest results will be obtained by
- retrieve only the assignments_questions rows for the given assignment, then join to the questions table get the related questions
- retireve all questions, join to all matching assignments_questions rows, then throw away the ones that aren't for assignment 208
see the difference?
like i said, mysql is smart enough to figure out that it should execute 1 instead of 2 (when it's an inner join)
with this in mind, i find that it makes ~way~ more sense to actually write the FROM clause the same way
because if you are looking at a strange query, trying to understand what it's doing (and often the author of the query is usually yourself, and you're trying to enhance it a couple months down the road), it's a ~lot~ easier to figure it out if it's written in such a way as to model what the query is actually trying to accomplish
i call the first table in the FROM clause the "driving" table, as it is what drives the successful and efficient retrieval of rows, and it is almost always the one that has the associated WHERE condition