Filtering before Join or after Join in SQL
explain, join, mysql, sql
Solution
1st one is better as it is easy to read and write and has the lower row scan value otherwise the queries have the same result set.
Problem
I have two queries to get the same data, they more or less have the same execution time. ``` SELECT name AS prog_name,d.chap_name,d.vid_name,d.idvideo FROM program JOIN (SELECT name AS chap_name,chapter.idprogram, c.vid_name,c.idvideo FROM chapter JOIN (SELECT a.name AS vid_name, a.idvideo, b.idchapter FROM video a JOIN (SELECT idvideo,idchapter,x.idchaptervideo FROM chaptervideo JOIN (SELECT idchaptervideo FROM prescriptionvideo WHERE idprescription=50)x ON x.idchaptervideo=chaptervideo.idchaptervideo) b ON b.idvideo=a.idvideo)c ON c.idchapter=chapter.idchapter) d ON d.idprogram=program.idprogram SELECT program.name AS prog_name,chapter.name as chap_name,video.name as vid_name,video.idvideo FROM prescriptionvideo JOIN chaptervideo ON prescriptionvideo.idchaptervideo=chaptervideo.idchaptervideo JOIN video on chaptervideo.idvideo=video.idvideo JOIN chapter on chaptervideo.idchapter=chapter.idchapter JOIN program on chapter.idprogram=program.idprogram where idprescription=50 ``` Can somebody guide me as to which one of them is better. I have used filtering before joins in the former and after joins in the latter. The MySQL explain shows more row scans in the former compared to the latter.