Gustavo Tarchiani Gustavo Tarchiani - 1 month ago 14
MySQL Question

MySQL query - better performance with more specificity on JOIN?

I'm writing a MySQL query and I have a doubt: wich of these codes (below) are faster? I think if I'm more specific in "left join" (second code) it should be faster. Is this approach correct?

SELECT m.*, p.nome AS nomeprocedimento
FROM medicos AS m
LEFT JOIN medicoprocedimento AS mp ON m.id = mp.medicoid
LEFT JOIN procedimentos AS p ON mp.procedimentoid = p.id
WHERE m.id = 123


OR

SELECT m.*, p.nome AS nomeprocedimento
FROM medicos AS m
LEFT JOIN medicoprocedimento AS mp ON m.id = mp.medicoid AND mp.medicoid = 123
LEFT JOIN procedimentos AS p ON mp.procedimentoid = p.id
WHERE m.id = 123

Answer

If the field is indexed, it usually doesn't make much difference, because the query optimizer will see that you're looking for a single value from the medicoprocedimento.medicoid (or medicos.id) column. So, it will usually do an index seek/scan on this value before it does the join even if you only have the filter in the where clause and not directly in the join.

That said, you should always check your execution plans to verify that this optimization is being done before you settle on your choice. There's no substitute for seeing what actually happens when you run the query.

Comments