php - SQL 查询执行时间过长

Question

我目前正在做一个需要我扫描 Public Whip Raw Data 的项目并返回 MP 名称列表(谁投票支持与输入的关键字相匹配的策略，例如“fox hunting”)。当前的 SQL 查询需要大约 30 秒才能完成执行，这太长了。

这是在“距离”表和“策略”表中查找的 SQL 查询。 (这是执行时间太长的原因)

$sql = "SELECT DISTINCT distance.mp_id from distance WHERE distance.distance < 0.2 AND distance.dream_id IN (SELECT dream_id from policy WHERE UPPER(policy.title) LIKE UPPER('%".$keyword."%')) ORDER BY distance.distance LIMIT 5";

这是仅回显 mp 名称的其余代码

$results = mysql_query($sql);
echo "<ul>";
while ($row = mysql_fetch_array($results)) {
    $mpid = $row['mp_id'];
    $sql = "SELECT mp.first_name,mp.last_name FROM mp WHERE mp_id = ".$mpid;
    $result = mysql_query($sql);
    $result = mysql_fetch_assoc($result);
    echo "<li>".$result['first_name']." ".$result['last_name']."</li>\n";
}
echo "</ul>";

Answer 1

这是您的查询:

SELECT DISTINCT distance.mp_id
from distance
WHERE distance.distance < 0.2 AND
      distance.dream_id IN (SELECT dream_id
                            from policy
                            WHERE UPPER(policy.title) LIKE UPPER('%".$keyword."%')
                           )
ORDER BY distance.distance
LIMIT 5;

在某些版本的 MySQL 中，带有子查询的 in 效率低下。我还假设 mp_id 对于表 distance 是唯一的。此查询可能会更好:

SELECT d.mp_id
from distance d
WHERE d.distance < 0.2 AND
      exists (select 1
              from policy p
              where UPPER(p.title) LIKE UPPER('%".$keyword."%') and
                    p.dream_id = d.dream_id
             )
ORDER BY d.distance
LIMIT 5;

通过在 policy(dream_id) 和可能的 distance(distance) 上建立索引，可以进一步改进此查询。

根据策略表的大小，影响性能的一个主要障碍是表达式 UPPER(policy.title) LIKE UPPER('%".$keyword."%')。如果您真的是说平等，那么请使用平等而不是 like 和通配符。如果您确实要在 title 列中存储多个关键字，请考虑将它们分解到一个单独的表中或使用全文搜索。