I try to perform the following query, but it seems to get stuck, and the MySQL server eats up all the CPU.
Can anyone see what the problem is:
select count(people.ID) from people where people.ID in (SELECT people.ID FROM people, event WHERE (people.ID = event.Owner AND event.Place like '%sh%' AND event.Type IN ('Birthday', 'Wedding')));
The ID is the key of the people table.
Here is the definition of the event table:
CREATE TABLE event (
ID INT UNSIGNED NOT NULL,
Owner INT UNSIGNED NOT NULL,
Type VARCHAR(100) default '',
Place VARCHAR(255) default '',
PRIMARY KEY (ID),
FOREIGN KEY Belongs_to_fk (Owner) REFERENCES people(ID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Here is the definition of the people table
CREATE TABLE people (
ID INT UNSIGNED NOT NULL,
PRIMARY KEY (ID),
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
The people table has more columns, but they should not be relevant.
Note: the inner query works perfectly when it's on its own.
Thanks
query goes into an endless loop - something is wrong
Moderator: General Moderators
-
davidklonski
- Forum Contributor
- Posts: 128
- Joined: Mon Mar 22, 2004 4:55 pm
What if rewrite your query as inner join:
?
What you were doing is extremely slow because first you were getting all the ids matching your criterias an then counting all the rows which have that id. Why not just count(distinct people.ID) rows of inner query?
It's not the reason for MySQL to hang, though.
Code: Select all
select count(distinct people.ID) from people inner join event on people.ID=event.Owner where event.Place like '%sh%' an event.Type in ('Birthday', 'Wedding');What you were doing is extremely slow because first you were getting all the ids matching your criterias an then counting all the rows which have that id. Why not just count(distinct people.ID) rows of inner query?
It's not the reason for MySQL to hang, though.
-
davidklonski
- Forum Contributor
- Posts: 128
- Joined: Mon Mar 22, 2004 4:55 pm