query goes into an endless loop - something is wrong

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
davidklonski
Forum Contributor
Posts: 128
Joined: Mon Mar 22, 2004 4:55 pm

query goes into an endless loop - something is wrong

Post by davidklonski »

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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

What if rewrite your query as inner join:

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

Post by davidklonski »

I found the bug.
The Owner column in the event table wasn't indexed and that caused the server to spend a lot of time searching for matching results.
Post Reply