Why left join make the query so slow?

[ayok]

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

[fenway]

The only reason to use LEFT JOIN is to get back records from factuur that don't have matches in bestelling.

[ayok]

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?

[fenway]

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.

[ayok]

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?

[fenway]

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.