LEFT JOIN非常大的表格 [英] LEFT JOIN on very large tables

问题描述

我的数据库有两个表 - 电影有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

解决方案

在文章< d7 ********** @ news8.svr.pol.co.uk>，

" StealthBananaT" <无*** @ nowhere.com>写道：

我的数据库有两个表 - 电影有10,000条记录，评论有20,000条记录。每当我尝试列出所有电影及其评论的数量时，MySQL都会锁定，我必须重启服务。

这里是SQL ......

选择电影。*，COUNT（reviews.id）
来自电影LEFT JOIN评论ON films.id = reviews.filmID
GROUP BY films.id
LIMIT 0,100;

JOIN有效，但没有回复没有评论的电影记录。请
帮助！

是film.id和reviews.filmID索引吗？


-

DeeDee，不要按那个按钮！迪迪！没有！ Dee ...

" StealthBananaT" <无*** @ nowhere.com>写道：

我的数据库有两个表 - 电影有10,000条记录，评论有20,000条记录。每当我尝试列出所有电影及其评论的数量时，MySQL都会锁定，我必须重启服务。

这里是SQL ......

选择电影。*，COUNT（reviews.id）
来自电影LEFT JOIN评论ON films.id = reviews.filmID
GROUP BY films.id
LIMIT 0,100;

JOIN有效，但没有回复没有评论的电影记录。请帮忙！

除非id，否则这根本不起作用。是电影中唯一的字段。

GROUP BY子句必须包含SELECT列表中的每个字段，而不是
汇总函数。


你有关于films.id，reviews.id和reviews.filmID的索引吗？

-

- Tim Roberts， ti ** @ probo.com

Providenza& Boekelheide，Inc。

" Tim Roberts" < TI ** @ probo.com>在留言新闻中写道：b6 ******************************** @ 4ax.com ...

" StealthBananaT" <无*** @ nowhere.com>写道：

我的数据库有两个表 - 电影有10,000条记录，评论有20,000条记录。每当我试图列出所有的电影和评论的数量时，我都会锁定MySQL并且我必须重启服务。

这里是SQL ......

选择电影。*，COUNT（reviews.id）
来自电影LEFT JOIN评论ON films.id = reviews.filmID
GROUP BY films.id
LIMIT 0,100;

JOIN有效，但没有返回没有评论的电影记录。请帮忙！

除非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

解决方案

In article <d7**********@news8.svr.pol.co.uk>,
"StealthBananaT" <no***@nowhere.com> wrote:

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!

Are films.id and reviews.filmID indexes?

--
DeeDee, don''t press that button! DeeDee! NO! Dee...

"StealthBananaT" <no***@nowhere.com> wrote:

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!

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.

"Tim Roberts" <ti**@probo.com> wrote in message news:b6********************************@4ax.com...

"StealthBananaT" <no***@nowhere.com> wrote:

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!

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非常大的表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！