为什么这个MySQL查询这么慢? [英] Why is this MySQL query so slow?

问题描述

我们正在努力使此查询在可容忍的时间内运行:

We're working really hard to get this query to run in a tolerable amount of time:

SELECT `User`.`id`,
FROM `users` AS `User` 
LEFT JOIN `attempts` AS `Attempt` ON (`Attempt`.`user_id` = `User`.`id` AND `Attempt`.`test_id` != 875) 
LEFT JOIN `resumes` AS `Resume` ON (`Resume`.`user_id` = `User`.`id`) 
WHERE `Attempt`.`test_id` = 964 AND `Attempt`.`score` > 10 AND `Resume`.`has_file` = 1 AND `Resume`.`user_id` = `User`.`id` 

总共约有50,000个用户行.

There are approximately 50,000 total user rows.

当LIMIT小于或等于1000时，查询几乎是瞬时的.但是，如果不存在此限制，它将在300秒(我们的测试环境中允许的最大值)后超时.

The query is almost instantaneous when there is a LIMIT of 1000 or less. But if the limit is not present it times out after 300 seconds (the max allowed on our testing environment).

我们需要能够在10秒或更短的时间内返回查询的所有结果.有时，这大约是10,000条记录.这是不现实的吗?还是我们在做次优的选择，使查询如此缓慢.

We need to be able to return all of the result from the query in preferably 10 seconds or less. At times this will be around 10,000 records. Is this unrealistic? Or are we doing something sub-optimal that is making this query so slow.

如果有意义，当我从命令行(而不是通过我的PHP应用程序)运行此查询时，即使返回10K记录，它也非常快.

If it's relevant, when I run this query from the command line (not through my PHP app) it is very fast, even when returning 10K records.

*更新:* EXPLAIN显示索引未用于已连接的表之一

*Update: * EXPLAIN shows that indexes are not getting used for one of the joined tables

推荐答案

您的JOIN和WHERE子句非常混乱，就像上面我的评论中提到的问题一样.

Your JOIN and WHERE clauses are horribly confused, like the question referenced in my comment above.

尝试一下:

SELECT `User`.`id`
FROM `users` AS `User` 
INNER JOIN `attempts` AS `Attempt` ON `Attempt`.`user_id` = `User`.`id` 
INNER JOIN `resumes` AS `Resume` ON `Resume`.`user_id` = `User`.`id`
WHERE `Attempt`.`test_id` = 964 
    AND `Attempt`.`score` > 10 
    AND `Resume`.`has_file` = 1 

这篇关于为什么这个MySQL查询这么慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！