query goes into an endless loop - something is wrong

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=InnoDB DEFAULT CHARSET=latin1;

Here is the definition of the people table
CREATE TABLE people (
ID INT UNSIGNED NOT NULL,
PRIMARY KEY (ID),
) ENGINE=InnoDB 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

onion2k

Are you using MySQL 4.1? Coz if you're not then your subselect isn't going to work.

davidklonski

I am using MySQL 5.0

I am using Subqueries throughout my application without any problems.
There is a specific problem with this one though....

nemonoman

don't know for sure why you've got the error, but since you know the subquery works, and since you can shortcut the problem by simply counting the subquery, why not stop there?

SELECT count(*) FROM people, event WHERE (people.ID = event.Owner AND event.Place like '%sh%' AND event.Type IN ('Birthday', 'Wedding'));

davidklonski

The query I was asking about is part of a complete solution.
The inner query may be replaced by other more complex queries.
I need the format of the query to remain like this.

Assuming that the is the format of the query, what is wrong with it? Why does it cause the MySQL server to freeze?

nemonoman

Like I say, I have only some guesses: My first guess would be because you are including the people table in both the main and subquery. If you have another table that uses people.id as a foreign key, try using that for your main query.

davidklonski

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

nemonoman

Why then did the subquery work effectively before?

"Note: the inner query works perfectly when it's on its own. "

Or was that not entirely true?

davidklonski

I don't know.

I am new to MySQL and SQL so I am learning as I go along.
The inner query was exactly as I wrote. I didn't change anything in it.

nemonoman

Then what prompted you to discover this was the problem? "I found the bug. The Owner column in the event table wasn't indexed and that cause the server to spend a lot of time searching for matching results."

davidklonski

because when I set an index on the Owner column, the query started working perfectly.

I am assuming it was the bug... I may be wrong though