问题描述
我的数据库有两个表 - 电影有10,000条记录，评论有20,000条记录。每当我尝试列出所有电影和其评论的
计数时，MySQL就会锁定，我必须重启服务。
这是' SQL ...
SELECT电影。*，COUNT（reviews.id）
来自电影LEFT JOIN评论ON films.id = reviews.filmID
GROUP BY films.id
LIMIT 0,100;
JOIN有效，但没有返回没有评论的电影记录。请帮忙！
-
StealthBanana
是film.id和reviews.filmID索引吗？
-
DeeDee，不要按那个按钮！迪迪！没有！ Dee ...
除非id，否则这根本不起作用。是电影中唯一的字段。
GROUP BY子句必须包含SELECT列表中的每个字段，而不是
汇总函数。
你有关于films.id，reviews.id和reviews.filmID的索引吗？
-
- Tim Roberts，
Providenza& Boekelheide，Inc。
除非id，否则这根本不起作用。是电影中唯一的字段。
GROUP BY子句必须包含SELECT列表中不是聚合函数的每个字段。
你有关于films.id，reviews.id的索引吗？ ，和reviews.filmID？
代码绝对有效 - 我用较少的记录尝试过它。
那里在reviews.filmID上没有索引。这是一个外键，以便我可以关注每个评论的电影。
-
StealthBanana
My database has two tables - films has 10,000 records and reviews has 20,000 records. Whenever I try to list all the films and the
count of its reviews, MySQL locks and I have to restart the service.
Here''s the SQL...
SELECT films.*, COUNT(reviews.id)
FROM films LEFT JOIN reviews ON films.id = reviews.filmID
GROUP BY films.id
LIMIT 0, 100;
JOIN works but doesn''t return records for films with no reviews. Please help!
--
StealthBanana
Are films.id and reviews.filmID indexes?
--
DeeDee, don''t press that button! DeeDee! NO! Dee...
This shouldn''t work at all unless "id" is the only field in "films". The
GROUP BY clause must include every field in the SELECT list that isn''t in
an aggregate function.
Do you have indexes on films.id, reviews.id, and reviews.filmID?
--
- Tim Roberts, ti**@probo.com
Providenza & Boekelheide, Inc.
This shouldn''t work at all unless "id" is the only field in "films". The
GROUP BY clause must include every field in the SELECT list that isn''t in
an aggregate function.
Do you have indexes on films.id, reviews.id, and reviews.filmID?
The code definitely works - I''ve tried it with fewer records.
There''s no index on reviews.filmID. That is a foreign key so that I can follow which film each review is of.
--
StealthBanana
这篇关于LEFT JOIN非常大的表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！