以下查询执行时间过长.如何优化它 [英] Following query taking too long to execute. How to optimize it

问题描述

我使用以下查询来获取years 和当年上映的电影数量，其中(指电影)有女演员.

I use the following query to get years and the number of movies released in that year which(referring to a movie) has a female actor.

执行成功，但是执行需要4分钟.
如何在更短的时间内获得结果?

It executes successfully, but it is taking 4 minutes to execute.
How do I get results in less time?

此外，在此执行期间，我的磁盘使用率超过 70%.

Also, my disk use goes to more than 70% during this execution.

SELECT m.year, COUNT(m.id)
FROM movies m
JOIN roles r ON m.id=r.movie_id
JOIN actors a ON r.actor_id=a.id
WHERE a.gender='F'
GROUP BY m.year;

推荐答案

我觉得你可以用

存在

而不是 JOIN.

SELECT m.year, COUNT(m.id) FROM movies m 
where 
exists (select * from roles r where r.movie_id=m.id and 
exists(select * from actors a where a.id=r.actor_id and a.gender='F'))
group by  m.year;

显示每年的电影总数以及上述输出.

To display Total Movies Per Year along with above output.

select t1.year,t1.count,t2.total from 
(
SELECT m.year as year, COUNT(m.id) as count FROM movies m 
where exists (select * from roles r where r.movie_id=m.id and exists(select * from actors a where a.id=r.actor_id and a.gender='F'))
group by  m.year
)t1 
join
(select year,count(m.id) as total from movies m group by m.year) t2
on t1.year=t2.year;

这篇关于以下查询执行时间过长.如何优化它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！