mysql循环查sql_循环中的MySQL查询与使⽤SQL连接
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?
mysql下载链接
解决⽅案
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.

版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。