SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2004
    Location
    United States
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Slow query problem

    Hey guys, hopefully you can help me with this one.

    I have this query in my slow query log.

    # Query_time: 10 Lock_time: 0 Rows_sent: 15 Rows_examined: 1644102
    select P.id, M.username, P.name, T.name type, P.color, P.price, P.location, M.id userID from table1 P, table2 T, table3 M where (P.owner=M.id)and(P.pet_type=T.id) order by P.name ASC LIMIT 539235, 15;
    Now obviously something is wrong...it takes 10 seconds and it has 1.6 million rows being examined. I can't figure out where I'm going wrong though.

    Here the the index's I have:

    Table - P
    Keyname: ownerptype
    Field 1: owner
    Field 2: pet_Type

    Keyname: name
    Field 1: name

    Table - M
    Keyname: PRIMARY
    Field 1: id

    Table - T
    Keyname: PRIMARY
    Field 1: id

    From what I can tell I'm using all the correct index's. When I run explain on the query it gives me the following information:

    id select_type table type possible_keys key key_len ref rows Extra
    1 SIMPLE P index ownerptype name 252 NULL 900708
    1 SIMPLE M eq_ref PRIMARY PRIMARY 4 gubbax_new2.P.owner 1
    1 SIMPLE T eq_ref PRIMARY PRIMARY 4 gubbax_new2.P.pet_type 1
    Any idea what I'm doing wrong here?

    Thanks!

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,998
    Mentioned
    100 Post(s)
    Tagged
    0 Thread(s)
    In the SELECT clause, which table is type coming from? How are the tables related?

    Can you show a CREATE TABLE for the tables?

    You should use the JOIN syntax:

    Code SQL:
    SELECT
          p.id
        , m.username
        , p.name
        , t.name
        , TYPE
        , p.color
        , p.price
        , p.location
        , m.userID
    FROM
        M
    INNER JOIN
        P
    ON
        p.owner = M.id
    INNER JOIN
        T
    ON
        P.pet_type=T.id
    ORDER BY
        P.name ASC
    LIMIT
        539235, 15
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Try adding an index on only pet_Type for table1, and maybe one on only owner as well. Right now you only have a combined key there, and it might not work well since you're joining with only half of it. Just guessing, you'll have to try.

  4. #4
    SitePoint Enthusiast
    Join Date
    Feb 2004
    Location
    United States
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, so the query I had in the form of INNER JOIN's would look like this, SpacePhoenix. Type and userID were just renamed fields.

    Code SQL:
    SELECT
          P.id
        , M.username
        , P.name
        , T.name AS `type`
        , P.color
        , P.price
        , P.location
        , M.id AS `userID`
    FROM
        table1 M
    INNER JOIN
        table2 P
    ON
        P.owner = M.id
    INNER JOIN
        table3 T
    ON
        P.pet_type=T.id
    ORDER BY
        P.name ASC
    LIMIT
        539235, 15

    In this format, the query still takes 10 seconds and returns this EXPLAIN:
    id select_type table type possible_keys key key_len ref rows Extra
    1 SIMPLE P index ownerptype name 252 NULL 900536
    1 SIMPLE M eq_ref PRIMARY PRIMARY 4 gubbax_new2.P.owner 1
    1 SIMPLE T eq_ref PRIMARY PRIMARY 4 gubbax_new2.P.pet_type 1


    Here are my create table's...

    Code SQL:
    --
    -- Table structure for table `table1`
    --
     
    CREATE TABLE IF NOT EXISTS `table1` (
      `id` INT(11) NOT NULL AUTO_INCREMENT,
      `email` VARCHAR(50) NOT NULL DEFAULT '',
      `password` VARCHAR(50) NOT NULL DEFAULT '',
      `first_name` VARCHAR(50) NOT NULL DEFAULT '',
      `last_name` VARCHAR(50) NOT NULL DEFAULT '',
      `dob` VARCHAR(50) NOT NULL DEFAULT '',
      `gender` VARCHAR(50) NOT NULL DEFAULT '',
      `birthday` INT(11) NOT NULL,
      `nextbirth` INT(11) NOT NULL,
      `age` INT(11) NOT NULL DEFAULT '0',
      `signup_date` VARCHAR(50) NOT NULL DEFAULT '',
      `signup_ip` VARCHAR(50) NOT NULL DEFAULT '',
      `last_login` VARCHAR(50) NOT NULL DEFAULT '',
      `activated` INT(11) NOT NULL DEFAULT '0',
      `username` VARCHAR(50) NOT NULL DEFAULT '',
      `money` INT(11) NOT NULL DEFAULT '0',
      `location` VARCHAR(50) NOT NULL DEFAULT '',
      `credits` INT(11) NOT NULL DEFAULT '0',
      `user_level` INT(11) NOT NULL DEFAULT '0',
      `profile` text NOT NULL,
      `sig` text NOT NULL,
      `avatar` INT(11) NOT NULL DEFAULT '1',
      `pid` INT(11) NOT NULL DEFAULT '0',
      `prank` INT(11) NOT NULL DEFAULT '0',
      `banned` INT(11) NOT NULL DEFAULT '0',
     
      PRIMARY KEY  (`id`),
      KEY `username` (`username`(1)),
      KEY `money` (`money`),
      KEY `viewNews` (`viewNews`),
      KEY `lastseentime` (`lastseentime`),
      KEY `email` (`email`),
      KEY `password` (`password`),
      KEY `partyID` (`partyID`),
      KEY `partyRank` (`partyRank`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=119563 ;
     
    -- --------------------------------------------------------
     
    --
    -- Table structure for table `table2`
    --
     
    CREATE TABLE IF NOT EXISTS `table2` (
      `id` INT(11) NOT NULL AUTO_INCREMENT,
      `owner` INT(11) NOT NULL DEFAULT '0',
      `name` VARCHAR(250) NOT NULL DEFAULT '',
      `pet_type` INT(50) NOT NULL DEFAULT '0',
      `image_path` VARCHAR(250) NOT NULL DEFAULT '',
      `gender` VARCHAR(250) NOT NULL DEFAULT '',
      `age` INT(11) NOT NULL DEFAULT '0',
      `level` INT(11) NOT NULL DEFAULT '0',
      `health` INT(11) NOT NULL DEFAULT '0',
      `current_health` INT(11) NOT NULL DEFAULT '0',
      `hunger` INT(11) NOT NULL DEFAULT '0',
      `power` INT(11) NOT NULL DEFAULT '0',
      `defense` INT(11) NOT NULL DEFAULT '0',
      `agility` INT(11) NOT NULL DEFAULT '0',
      `intelect` INT(11) NOT NULL DEFAULT '0',
      `element` INT(11) NOT NULL DEFAULT '0',
      `rarity` INT(11) NOT NULL DEFAULT '0',
      `emotion` INT(11) NOT NULL DEFAULT '0',
      `characteristics` text NOT NULL,
      `selected` INT(11) NOT NULL DEFAULT '0',
      `color` VARCHAR(50) NOT NULL DEFAULT '',
      `location` INT(5) NOT NULL DEFAULT '0'
      PRIMARY KEY  (`id`),
      KEY `hunger` (`hunger`),
      KEY `intellect` (`intelect`),
      KEY `power` (`power`),
      KEY `defense` (`defense`),
      KEY `agility` (`agility`),
      KEY `location` (`location`),
      KEY `ownerptype` (`owner`,`pet_type`),
      KEY `name` (`name`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1720774 ;
     
    --
    -- Table structure for table `table3`
    --
     
    CREATE TABLE IF NOT EXISTS `table3` (
      `id` INT(5) NOT NULL AUTO_INCREMENT,
      `name` VARCHAR(25) NOT NULL DEFAULT '',
      `image_path` VARCHAR(50) NOT NULL DEFAULT '',
      `health` INT(5) NOT NULL DEFAULT '0',
      `power` INT(5) NOT NULL DEFAULT '0',
      `defense` INT(5) NOT NULL DEFAULT '0',
      `agility` INT(5) NOT NULL DEFAULT '0',
      `intelect` INT(5) NOT NULL DEFAULT '0',
      `element` INT(5) NOT NULL DEFAULT '0',
      `rarity` INT(5) NOT NULL DEFAULT '0',
      `item_id` INT(5) NOT NULL DEFAULT '0',
      `incubation` INT(5) NOT NULL,
      PRIMARY KEY  (`id`),
      KEY `name` (`name`),
      KEY `element` (`element`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=45 ;
     
    -- --------------------------------------------------------


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
  •