SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    query goes into an endless loop - something is wrong

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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:
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •