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=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

[r937]

if you are actually executing a query with a subquery, then you must be on 4.1, which is still in "alpha" release status

in any case, you can rewrite your query like this:

select count(distinct people.ID)
  from people
inner
  join event 
    on people.ID 
     = event.Owner 
 where event.Place like '%sh%' 
   and event.Type in ('Birthday', 'Wedding')

the DISTINCT is necessary because while your subquery can return the same person for more than one event, your outer query's use of IN (subquery) effectively counts just distinct people

[davidklonski]

Thanks for the reply

I am using MySQL 5.0

I have tried your query and I am getting the same results.
Maybe there is something wrong with the way the schemas are defined?

Here are the definitions:
CREATE TABLE people (
First_name VARCHAR(255) default '',
Gender CHAR default 'U',
ID INT UNSIGNED NOT NULL,
Last_name VARCHAR(255) default '',
Title VARCHAR(255) default '',
PRIMARY KEY (ID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

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;

thanks for the help

[r937]

if "same results" means endless loop, i would definitely reinstall, but be careful, i am so not a dba

5.0 is way less stable than 4.1

[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.