ayok Posted September 7, 2011 Share Posted September 7, 2011 Hi, I have this query $sql = "SELECT"; $sql .= " factuur.id, DATE_FORMAT(factuur.factuurdatum, '%d-%m-%Y') AS factuurdatum, factuur.prefix, factuur.volgnr,"; $sql .= " SUM(bestelling.bedrag) AS bedrag"; $sql .= " FROM {$db['factuur']} AS factuur"; $sql .= " LEFT JOIN {$db['bestelling']} AS bestelling ON bestelling.factuur_id=factuur.id"; $sql .= " WHERE factuur.uid=" . $uid; $sql .= " GROUP BY factuur.id"; $sql .= " ORDER BY factuur.factuurdatum DESC"; And it works sooo slow when I got hundreds of records in "bestelling" table. Then I change the query into this $sql = "SELECT"; $sql .= " factuur.id, DATE_FORMAT(factuur.factuurdatum, '%d-%m-%Y') AS factuurdatum, factuur.prefix, factuur.volgnr,"; $sql .= " SUM(bestelling.bedrag) AS bedrag"; $sql .= " FROM {$db['factuur']} AS factuur"; $sql .= ", {$db['bestelling']} AS bestelling WHERE bestelling.factuur_id=factuur.id"; $sql .= " AND factuur.uid=" . $uid; $sql .= " GROUP BY factuur.id"; $sql .= " ORDER BY factuur.factuurdatum DESC"; It goes a lot faster. But why? Should I stop using left join? What's the different between these queries? Will I get different result?? Thank you, ayok Link to comment https://forums.phpfreaks.com/topic/246611-why-left-join-make-the-query-so-slow/ Share on other sites More sharing options...
fenway Posted September 7, 2011 Share Posted September 7, 2011 The only reason to use LEFT JOIN is to get back records from factuur that don't have matches in bestelling. Link to comment https://forums.phpfreaks.com/topic/246611-why-left-join-make-the-query-so-slow/#findComment-1266393 Share on other sites More sharing options...
ayok Posted September 7, 2011 Author Share Posted September 7, 2011 Hi thanks, Does it mean that with left join it will return more results? Does "left" means table on the left, mentioned in the query, got the priority? But why is it slower with left join? Link to comment https://forums.phpfreaks.com/topic/246611-why-left-join-make-the-query-so-slow/#findComment-1266417 Share on other sites More sharing options...
fenway Posted September 7, 2011 Share Posted September 7, 2011 If there are non-matching rows in the right table, then yes, it will retrieve more rows. It's not about slow -- it's about what you want to retrieve. Link to comment https://forums.phpfreaks.com/topic/246611-why-left-join-make-the-query-so-slow/#findComment-1266424 Share on other sites More sharing options...
ayok Posted September 7, 2011 Author Share Posted September 7, 2011 But the query is much faster after I change the left join query to normal where query... Maybe because there are many records in left table? Link to comment https://forums.phpfreaks.com/topic/246611-why-left-join-make-the-query-so-slow/#findComment-1266428 Share on other sites More sharing options...
fenway Posted September 7, 2011 Share Posted September 7, 2011 But the query is much faster after I change the left join query to normal where query... Maybe because there are many records in left table? Probably -- many records in the left table with non-matching rows in the right. Link to comment https://forums.phpfreaks.com/topic/246611-why-left-join-make-the-query-so-slow/#findComment-1266432 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.