优化此 SQL 查询 [英] Optimize this SQL query

问题描述

我在 foreach 循环中有一个 SQL 查询.有时可能有很多，我的意思是有很多查询要做，取决于几个标准，最多可能有 78 个查询.

I've got a SQL query within a foreach loop. Sometimes there can be many, and I mean a lot of queries to do, depending on several criteria, up to 78 queries potentially.

现在，我知道过早优化是万恶之源，但我不想看到 78 个查询 - 它只是不健康.

Now, I know that premature optimisation is root cause of all evil, but I don't want to see 78 queries - it's just not healthy.

代码如下:

$crumbs = explode(",", $user['data']['depts']);

foreach ($crumbs as &$value) {
    $data = $db->query("SELECT id FROM tbl_depts WHERE id = '" . $value . "'");
    $crumb = $data->fetch_assoc();
    $dsn = $db->query("SELECT msg, datetime FROM tbl_motd WHERE deptid = '" . $value . "'");
    $motd = $dsn->fetch_assoc();
    if ($motd['msg'] != "") {
        <?php echo $motd['msg']; ?>
    }
}

我能做得更好吗?

推荐答案

使用 IN MySQL 运算符 用于搜索 id 的一组值:

$ids = '"' . implode('", "',$crumbs) . '"';
$query1 = "SELECT id FROM tbl_depts WHERE id IN (" . $ids . ")";
$query2 = "SELECT msg, datetime FROM tbl_motd WHERE deptid IN (" . $ids . ")";

因此您不需要使用 foreach 循环检索您需要的所有数据，因此您将只有 2 个查询而不是 78 个.

And so you will not need to retrieve all data you need using foreach loop, so you will have only 2 queries instead of 78.

示例:我有一个名为 table 的表，其中包含 10 条记录，其 ID 为:1、2、3、4、5、6、7、8、9、10(自动递增).我知道我需要 ID 为 1、5、8 的记录.我的查询将是:

Example: I have a table named table with 10 records which ids are: 1,2,3,4,5,6,7,8,9,10 (auto-incremented). I know I need records with ids 1,5,8. My query will be:

$sql = "SELECT * FROM `table` WHERE id in (1,5,8);";

而且我不明白如果不修改 $crubmsforeach 循环中使用 & 运算符> 数组值.

And I don't understand why do you need to use & operator in foreach loop if you don't modify the $crubms arrays values.

这篇关于优化此 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！