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
13  2560 
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
"Michael Vilain" <vi****@spamcop.net> wrote in message news:vi**************************@comcast.dca.giga news.com... 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?
Both tables have id as their index. filmID is a foreign key so that I know which film the review is of. Here's the SQL that
created both tables...
CREATE TABLE `films` (
`id` int(10) unsigned NOT NULL auto_increment,
`title` varchar(100) NOT NULL default '',
PRIMARY KEY (`id`)
)
CREATE TABLE `reviews` (
`id` int(10) unsigned NOT NULL auto_increment,
`filmID` int(10) unsigned NOT NULL default '0',
`author` varchar(20) NOT NULL default '',
`date` int(10) unsigned NOT NULL default '0',
`region` tinyint(1) unsigned NOT NULL default '0',
`review` longtext NOT NULL,
`rating` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (`id`)
)
--
StealthBanana
On Wed, 25 May 2005 12:52:08 +0100, StealthBananaT wrote: My database has two tables - films has 10,000 records and reviews has 20,000 records.
SELECT films.*, COUNT(reviews.id)
The code definitely works - I've tried it with fewer records.
Try restricting the number of fields you select from films to just the
title, for example.
--
Firefox Web Browser - Rediscover the web - http://getffox.com/
Thunderbird E-mail and Newsgroups - http://gettbird.com/
On Wed, 25 May 2005 13:54:17 +0200, I wrote: Try restricting the number of fields you select from films to just the
title, for example.
Oh, I see on your other reply `films` *is* just the title... (and id).
--
Firefox Web Browser - Rediscover the web - http://getffox.com/
Thunderbird E-mail and Newsgroups - http://gettbird.com/
In article <d7**********@newsg3.svr.pol.co.uk>,
"StealthBananaT" <no***@nowhere.com> wrote: "Michael Vilain" <vi****@spamcop.net> wrote in message
"news:vi**************************@comcast.dca.gig anews.com... 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?
Both tables have id as their index. filmID is a foreign key so that I know
which film the review is of. Here's the SQL that
created both tables...
CREATE TABLE `films` (
`id` int(10) unsigned NOT NULL auto_increment,
`title` varchar(100) NOT NULL default '',
PRIMARY KEY (`id`)
)
CREATE TABLE `reviews` (
`id` int(10) unsigned NOT NULL auto_increment,
`filmID` int(10) unsigned NOT NULL default '0',
`author` varchar(20) NOT NULL default '',
`date` int(10) unsigned NOT NULL default '0',
`region` tinyint(1) unsigned NOT NULL default '0',
`review` longtext NOT NULL,
`rating` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (`id`)
)
In order to not do a linear table scan, you must create indices on all
fields that require random access or are part of a join. Create an
index for the reviews.filmID field.
--
DeeDee, don't press that button! DeeDee! NO! Dee...
But you're joining the reviews table on reviews.filmID -- all the more
reason to create an index on it. With no index, the database is likely
scanning the reviews table for every row in films...in other words,
it's scanning through 20,000 records 10,000 times over.
"ZeldorBlat" <ze********@gmail.com> wrote in message news:11**********************@g44g2000cwa.googlegr oups.com... But you're joining the reviews table on reviews.filmID -- all the more
reason to create an index on it. With no index, the database is likely
scanning the reviews table for every row in films...in other words,
it's scanning through 20,000 records 10,000 times over.
I've added an index to filmID (I think), but I'm having the same problem.
According to MySQLCC, the SQL that would create the reviews table now looks like this
CREATE TABLE `reviews` (
`id` int(10) unsigned NOT NULL auto_increment,
`filmID` int(10) unsigned NOT NULL default '0',
`author` varchar(20) NOT NULL default '',
`date` int(10) unsigned NOT NULL default '0',
`region` tinyint(1) unsigned NOT NULL default '0',
`review` longtext NOT NULL,
`rating` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `FOREIGN` (`filmID`)
);
Any help would be greatly appreciated.
--
StealthBanana
"Michael Vilain" <vi****@spamcop.net> wrote in message news:vi**************************@comcast.dca.giga news.com... In article <d7**********@newsg3.svr.pol.co.uk>,
"StealthBananaT" <no***@nowhere.com> wrote:
"Michael Vilain" <vi****@spamcop.net> wrote in message
"news:vi**************************@comcast.dca.gig anews.com... > 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?
>
Both tables have id as their index. filmID is a foreign key so that I know
which film the review is of. Here's the SQL that
created both tables...
CREATE TABLE `films` (
`id` int(10) unsigned NOT NULL auto_increment,
`title` varchar(100) NOT NULL default '',
PRIMARY KEY (`id`)
)
CREATE TABLE `reviews` (
`id` int(10) unsigned NOT NULL auto_increment,
`filmID` int(10) unsigned NOT NULL default '0',
`author` varchar(20) NOT NULL default '',
`date` int(10) unsigned NOT NULL default '0',
`region` tinyint(1) unsigned NOT NULL default '0',
`review` longtext NOT NULL,
`rating` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (`id`)
)
In order to not do a linear table scan, you must create indices on all
fields that require random access or are part of a join. Create an
index for the reviews.filmID field.
I'd like to thank everybody for their help, but the problem remains. I've tried making reviews.filmID an index but that seemed to
have no effect.
I have a third table called users, and when I try to count the number of reviews submitted by users I have exactly the same
problem...
SELECT users.name, COUNT(reviews.id)
FROM users LEFT JOIN reviews ON users.name = reviews.author
GROUP BY users.name;
This is the users table...
CREATE TABLE `users` (
`name` varchar(20) NOT NULL default '',
`password` varchar(40) NOT NULL default '',
`level` tinyint(1) unsigned NOT NULL default '0',
`email` varchar(100) NOT NULL default '',
`sessionID` varchar(100) default '0',
PRIMARY KEY (`name`)
);
I'm guessing that the problem is linked to the sheer volume of the data in the tables.
--
StealthBanana
In article <d7**********@newsg3.svr.pol.co.uk>,
"StealthBananaT" <no***@nowhere.com> wrote: "Michael Vilain" <vi****@spamcop.net> wrote in message
"news:vi**************************@comcast.dca.gig anews.com... In article <d7**********@newsg3.svr.pol.co.uk>,
"StealthBananaT" <no***@nowhere.com> wrote:
"Michael Vilain" <vi****@spamcop.net> wrote in message
"news:vi**************************@comcast.dca.gig anews.com...
> 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?
>
Both tables have id as their index. filmID is a foreign key so that I
know
which film the review is of. Here's the SQL that
created both tables...
CREATE TABLE `films` (
`id` int(10) unsigned NOT NULL auto_increment,
`title` varchar(100) NOT NULL default '',
PRIMARY KEY (`id`)
)
CREATE TABLE `reviews` (
`id` int(10) unsigned NOT NULL auto_increment,
`filmID` int(10) unsigned NOT NULL default '0',
`author` varchar(20) NOT NULL default '',
`date` int(10) unsigned NOT NULL default '0',
`region` tinyint(1) unsigned NOT NULL default '0',
`review` longtext NOT NULL,
`rating` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (`id`)
)
In order to not do a linear table scan, you must create indices on all
fields that require random access or are part of a join. Create an
index for the reviews.filmID field.
I'd like to thank everybody for their help, but the problem remains. I've
tried making reviews.filmID an index but that seemed to
have no effect.
I have a third table called users, and when I try to count the number of
reviews submitted by users I have exactly the same
problem...
SELECT users.name, COUNT(reviews.id)
FROM users LEFT JOIN reviews ON users.name = reviews.author
GROUP BY users.name;
This is the users table...
CREATE TABLE `users` (
`name` varchar(20) NOT NULL default '',
`password` varchar(40) NOT NULL default '',
`level` tinyint(1) unsigned NOT NULL default '0',
`email` varchar(100) NOT NULL default '',
`sessionID` varchar(100) default '0',
PRIMARY KEY (`name`)
);
I'm guessing that the problem is linked to the sheer volume of the data in
the tables.
what happens if you try using the "EXPLAIN" directive to have MySQL tell
you what it would try to do for your query?
--
DeeDee, don't press that button! DeeDee! NO! Dee...
On Thu, 26 May 2005 04:13:41 +0100, "StealthBananaT" <no***@nowhere.com> wrote: "Michael Vilain" <vi****@spamcop.net> wrote in message news:vi**************************@comcast.dca.giga news.com... In article <d7**********@newsg3.svr.pol.co.uk>,
"StealthBananaT" <no***@nowhere.com> wrote: "Michael Vilain" <vi****@spamcop.net> wrote in message
"news:vi**************************@comcast.dca.gig anews.com...
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)
Change that to films.id, count(reviews.id) and it make more sense given the
GROUP BY, although that's already been pointed out.
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?
Both tables have id as their index. filmID is a foreign key so that I know
which film the review is of. Here's the SQL that
created both tables...
CREATE TABLE `films` (
`id` int(10) unsigned NOT NULL auto_increment,
`title` varchar(100) NOT NULL default '',
PRIMARY KEY (`id`)
)
CREATE TABLE `reviews` (
`id` int(10) unsigned NOT NULL auto_increment,
`filmID` int(10) unsigned NOT NULL default '0',
`author` varchar(20) NOT NULL default '',
`date` int(10) unsigned NOT NULL default '0',
`region` tinyint(1) unsigned NOT NULL default '0',
`review` longtext NOT NULL,
`rating` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (`id`)
)
In order to not do a linear table scan, you must create indices on all
fields that require random access or are part of a join. Create an
index for the reviews.filmID field.
I'd like to thank everybody for their help, but the problem remains. I've tried making reviews.filmID an index but that seemed to
have no effect.
I have a third table called users, and when I try to count the number of reviews submitted by users I have exactly the same
problem...
SELECT users.name, COUNT(reviews.id)
FROM users LEFT JOIN reviews ON users.name = reviews.author
GROUP BY users.name;
I'm guessing that the problem is linked to the sheer volume of the data in the tables.
Tables with 10k and 20k rows are not large in terms of rows - but the thing
that catches my attention is the "longtext" field in review.
This could make the table physically quite large; if MySQL is scanning the
table even once, then that could take some time.
You might consider moving the `review` field out to a separate table, with
primary key the same as reviews. That way you can run your queries on the
review metadata without it having to work through the full text of the review.
mysql> select count(*) from films;
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.02 sec)
mysql> select count(*) from reviews;
+----------+
| count(*) |
+----------+
| 20000 |
+----------+
1 row in set (0.00 sec)
mysql> select films.id, count(*)
-> from films
-> left outer join reviews
-> on (films.id = reviews.filmID)
-> group by films.id
-> limit 0, 100;
+-----+----------+
| id | count(*) |
+-----+----------+
| 1 | 2 |
[ snipped ]
| 99 | 1 |
| 100 | 3 |
+-----+----------+
100 rows in set (0.02 sec)
That's on an ancient Pentium 200 whilst it's compiling something in the
background, with the tables filled with random-ish data, an index on filmID,
but the review field containing only the string 'a review'.
But load up the reviews table with 250 words in each 'review' field and things
take a turn for the worse; left it running for 15 minutes with no result.
--
Andy Hassall / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
On Thu, 26 May 2005 04:13:41 +0100, StealthBananaT wrote:
I'd like to thank everybody for their help, but the problem remains. I've tried making reviews.filmID an index but that seemed to
have no effect.
I have a third table called users, and when I try to count the number of reviews submitted by users I have exactly the same
problem...
LIMIT can be very ineffiecient at returning results, effectively.
In your case it'll have to calculate the number of reviews for 10,000
before returning 100 rows.
SELECT f.title, f.id, count( r.id )
FROM films f
LEFT JOIN reviews r ON ( f.id = r.filmid )
WHERE f.id > 0 AND f.id <= 100
GROUP BY f.id
Use the where clause for pagination, you'll find your system responds a
lot quicker ;-) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Petre Agenbag |
last post by:
Hi List
Me again.
I'm trying to return from multiple tables, the records that have field
"information_sent" between two dates.
The tables are all related by means of the id of the entry in the...
|
by: Soefara |
last post by:
Dear Sirs,
I am experiencing strange results when trying to optimize a LEFT JOIN
on 3 tables using MySQL.
Given 3 tables A, B, C such as the following:
create table A (
uniqueId int not...
|
by: Paul Bramscher |
last post by:
Here's one for pathological SQL programmers.
I've got a table of things called elements. They're components, sort of
like amino acids, which come together to form complex web pages -- as
nodes...
|
by: Eric |
last post by:
Hi Folks,
Lets assume I have three tables. Their layout is as follows. Please
note that tblPeople does not have an entry for Denver (this is my
problem)
tblCity
_________________
CityName ...
|
by: Dam |
last post by:
Using SqlServer :
Query 1 :
SELECT def.lID as IdDefinition,
TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour
FROM serveur.Data_tblDEFINITIONTABLEDECODES def,...
|
by: Steve |
last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three
"Left Outer Joins" in order to return every transaction for a specific
set of criteria.
Using three "Left Outer Joins"...
|
by: tricard |
last post by:
Good day all,
I have a large outer joined query that I want to have some criteria.
The select query is gathering all part numbers from tblPartNumbers,
left joining to tblPartNumberVendor (since...
|
by: Eitan M |
last post by:
Hello,
I want to do select like this :
select t1.col_2
from table_1 t1, table_2 t2
where t1.col_1 = t2.col_1 (+)
The above is correct syntax for Oracle.
What is the correct syntax for...
|
by: shanevanle |
last post by:
I have two tables that are pretty big. I need about 10 rows in the
left table and the right table is filtered to 5 rows as well. It
seems when I join the tables in the FROM clause, I have to...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM)
The start time is equivalent to 19:00 (7PM) in Central...
|
by: erikbower65 |
last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps:
1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal.
2. Connect to...
|
by: kcodez |
last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
|
by: Taofi |
last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same
This are my field names
ID, Budgeted, Actual, Status and Differences
...
|
by: DJRhino1175 |
last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this -
If...
|
by: DJRhino |
last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer)
If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _
310030356 Or 310030359 Or 310030362 Or...
|
by: lllomh |
last post by:
Define the method first
this.state = {
buttonBackgroundColor: 'green',
isBlinking: false, // A new status is added to identify whether the button is blinking or not
}
autoStart=()=>{
|
by: lllomh |
last post by:
How does React native implement an English player?
|
by: DJRhino |
last post by:
Was curious if anyone else was having this same issue or not....
I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
| |
