Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL query in a loop vs using a SQL join

Tags:

sql

php

mysql

I'm having an inner debate at my company about looping queries in this matter:

$sql = "
  SELECT foreign_key
  FROM t1";

foreach(fetchAll($sql) as $row)
{
  $sub_sql = "
    SELECT *
    FROM t2
    WHERE t2.id = " . $row['foreign_key'];

  foreach(fetchAll($sub_sql) as $sub_row)
  {
    // ...
  }
}

Instead of using an sql join like this:

$sql = "
  SELECT t2.*
  FROM t2
  JOIN t1
  ON t1.foreign_key = t2.id";

foreach(fetchAll($sql) as $row)
{
  // ...
}

Additional information about this, the database is huge, millions of rows.

I have of course searched an answer to this question, but nobody can answer this in a a good way and with a lot of up votes that makes me certain that one way is better then the other.

Question

Can somebody explain to me why one of thees methods is better then the other one?

like image 687
superhero Avatar asked Aug 23 '13 14:08

superhero


People also ask

Is in query faster than join?

The advantage of a join includes that it executes faster. The retrieval time of the query using joins almost always will be faster than that of a subquery. By using joins, you can maximize the calculation burden on the database i.e., instead of multiple queries using one join query.

Is a join faster than multiple queries?

I won't leave you in suspense, between Joins and Subqueries, joins tend to execute faster. In fact, query retrieval time using joins will almost always outperform one that employs a subquery. The reason is that joins mitigate the processing burden on the database by replacing multiple queries with one join query.

Which is better nested query or join?

A general rule is that joins are faster in most cases (99%). The more data tables have, the subqueries are slower. The less data tables have, the subqueries have equivalent speed as joins.

Does join improve performance?

Basically, join order DOES matter because if we can join two tables that will reduce the number of rows needed to be processed by subsequent steps, then our performance will improve.


1 Answers

The join method is generally considered better, if only because it reduces the overhead of sending queries back and forth to the database.

If you have appropriate indexes on the tables, then the underlying performance of the two methods will be similar. That is, both methods will use appropriate indexes to fetch the results.

From a database perspective, the join method is far superior. It consolidates the data logic in one place, making the code more transparent. It also allows the database to make optimizations that might not be apparent in application code.

like image 85
Gordon Linoff Avatar answered Sep 29 '22 21:09

Gordon Linoff