mysql查询执行时间太长

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

即使我查询的所有字段都已编入索引，但我的查询执行时间太长（4秒）。以下是查询和解释结果。有什么想法是什么问题？ （执行查询时，mysql CPU使用率最高可达100％

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 个解决方案

#1

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

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.

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