mysql查询执行时间太长 [英] mysql query taking too long to execute

问题描述

我有一个查询执行时间太长(4 秒)，即使我查询的所有字段都已编入索引.下面是查询和解释结果.任何想法是什么问题?(执行查询时，mysql CPU 使用率高达 100%

I have a query that is taking way too long to execute (4 seconds) even though all the fields i am querying against are indexed. Below are the query and the explain results. Any ideas what the problem is? (mysql CPU usage shoots up to 100% when executing the query

EXPLAIN SELECT count(hd.did) as NumPo, `hd`.`sid`, `src`.`Name`
FROM (`hd`)
JOIN `result` ON `result`.`did` = `hd`.`did`
JOIN `sf` ON `sf`.`fid` = `hd`.`fid`
JOIN `src` ON `src`.`sid` = `hd`.`sid`
WHERE `sf`.`tid` =  2
AND `result`.`set` =  'xxxxxxx'
GROUP BY `hd`.`sid`
ORDER BY `NumPo` DESC
LIMIT 10;

+----+-------------+--------------+--------+-------------------------+---------+---------+--------------------------+------+----------------------------------------------+
| id | select_type | table        | type   | possible_keys           | key     | key_len | ref                      | rows | Extra                                        |
+----+-------------+--------------+--------+-------------------------+---------+---------+--------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | sf           | ref    | PRIMARY,type            | type    | 2       | const                    |    4 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | hd           | ref    | PRIMARY,sid,fid         | FeedID  | 4       | f2.sf.fid                |    3 |                                              |
|  1 | SIMPLE      | result       | ALL    | resultset               | NULL    | NULL    | NULL                     | 5322 | Using where; Using join buffer               |
|  1 | SIMPLE      | src          | eq_ref | PRIMARY                 | PRIMARY | 4       | f2.hd.sid                |    1 |                                              |
+----+-------------+--------------+--------+-------------------------+---------+---------+--------------------------+------+----------------------------------------------+

推荐答案

|  1 | SIMPLE      | result       | ALL    | resultset               | NULL    | NULL    | NULL                     | 5322 | Using where; Using join buffer               |

看起来它没有在最大的表上使用索引.我无法猜测这个查询应该做什么，但看起来你在 result.set 上有一个索引，所以我会尝试向 result.did，看看它是否有帮助.

It looks like it's not using an index on the biggest table. I'm having trouble guessing what this query is supposed to do, but it looks like you have an index on result.set, so I'd try adding one to result.did and see if it helps.

这篇关于mysql查询执行时间太长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！