SQL查询执行时间过长

Question

我目前正在进行一个项目，要求我扫描公共桨原始数据并返回一个MP名称列表(他们投票支持的策略与输入的关键字匹配，例如“狐狸搜索”)。当前的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列中存储多个关键字，那么可以考虑将这些关键字拆分到一个单独的表中，或者使用全文搜索。