#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Posts
    82
    Rep Power
    12

    Slow query.. Copying to tmp table


    I have this query to get player stats which seems to take forever and is stuck on "Copying to tmp table" for minutes. Is there any way to rewrite it or change indexes perhaps?

    Code:
    $query = "SELECT DISTINCT p.* FROM #__bl_players as p, #__bl_players_team as t, #__bl_match_events as me, #__bl_match as m, #__bl_matchday as md WHERE me.match_id = m.id AND m.m_id=md.id  AND p.id=t.player_id AND t.team_id = ".$team_id." AND t.team_id=me.t_id ".($s_id?" AND md.s_id=".$s_id:"")." ORDER BY  p.first_name,p.last_name".$sql_limit;
    Does it matter if the storage engine is MyISAM or InnoDB?
    johnno
  2. #2
  3. Wiser? Not exactly.
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    5,935
    Rep Power
    4033
    You need to post your table structures, current indexes, and the output of EXPLAIN if you want some help.

    Some things to look into though would be to use proper JOINs rather than joining via the WHERE clause, and select only what you need rather than *.
    Recycle your old CD's, don't just trash them



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Posts
    82
    Rep Power
    12
    EXPLAIN gives this

    id select_type table type possible_keys key key_len ref rows Extra
    1 SIMPLE me ref t_id,match_id t_id 4 const 714 Using temporary; Using filesort
    1 SIMPLE t ref PRIMARY PRIMARY 4 const 121 Using index
    1 SIMPLE m eq_ref PRIMARY,m_id PRIMARY 4 me.match_id 1
    1 SIMPLE md eq_ref PRIMARY,s_id PRIMARY 4 m.m_id 1 Using where
    1 SIMPLE p eq_ref PRIMARY PRIMARY 4 t.player_id 1

    I've checked and the indexes seem to be fine..

    #__bl_players PRIMARY KEY (`id`)
    #__bl_players_team PRIMARY KEY (`team_id`,`player_id`)
    #__bl_match_events PRIMARY KEY (`id`),
    KEY `t_id` (`t_id`),
    KEY `match_id` (`match_id`),
    KEY `e_id` (`e_id`,`player_id`,`ecount`)
    #__bl_match as m PRIMARY KEY (`id`),
    KEY `m_id` (`m_id`)
    #__bl_matchday PRIMARY KEY (`id`),
    KEY `s_id` (`s_id`)
    johnno
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,442
    Rep Power
    1688
    I'd start off using explicit JOIN syntax, moving some of thw conditions currently in your WHERE clause to the relevant ON conditions you'd link to the JOINs. I'd offer a suggested first-ut but not knowing php have no idea what
    Code:
    ($s_id?" AND md.s_id=".$s_id:"")
    is doing!
    Also, as kicken says, drop the 'select *' and specify each field you actually need.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,143
    Rep Power
    4274
    Originally Posted by ohnder
    Is there any way to rewrite it
    yes

    Code:
    SELECT DISTINCT p.*
       FROM #__bl_players_team as t
    INNER
      JOIN #__bl_players as p
        ON p.id = t.player_id 
    INNER
      JOIN #__bl_match_events as me
        ON me.t_id = t.team_id
    INNER
      JOIN #__bl_match as m
        ON m.id = me.match_id
    INNER
      JOIN #__bl_matchday as md 
        ON md.id = m.m_id  
       AND md.s_id=".$s_id:"")."
     WHERE t.team_id = ".$team_id."
       AND ".($s_id?" 
    ORDER 
        BY p.first_name
         , p.last_name" 
         .$sql_limit;
    Originally Posted by ohnder
    Does it matter if the storage engine is MyISAM or InnoDB?
    no
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Posts
    82
    Rep Power
    12
    I now did specify the * (three columns) but that made 0 difference.
    $s_id referes to s_id column in #__bl_matchday. It can be either specified or 0 (meaning all rows)..
    johnno
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Posts
    82
    Rep Power
    12
    Thanx r937. I tested but the end result takes just as long..
    Could the problem be with the provider?
    johnno
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,143
    Rep Power
    4274
    Originally Posted by ohnder
    Thanx r937. I tested but the end result takes just as long..
    that's to be expected, it was just a rewrite of your original query using explicit JOIN syntax (performance would not be different)

    Originally Posted by ohnder
    Could the problem be with the provider?
    possibly, but keep investigating your indexes until you exhaust those possibilities
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    If you are sure about the indexes (and my brief look at your explain didn't really suggest anything else).

    Then the problem could be the index cache configuration (or the overall memory in the db server).

    I've seen the same error message several times when RAM configuration for index cache has been very small and index reads have to be performed against disk which is very slow. The error message throws you off, but the real problem has been actually finding the data.

    You didn't say if you where using MyISAM or InnoDB?

    But with MyISAM you need to check the setting of key_buffer_size and the SHOW STATUS equivalents (although don't know if the provider gives you permission to view these or not).
    http://dev.mysql.com/doc/refman/5.6/...ey_buffer_size

    If you are using InnoDB then you need to perform a similar check on the configuration of:
    http://dev.mysql.com/doc/refman/5.6/...ffer_pool_size

    Good luck!
    /Stefan

IMN logo majestic logo threadwatch logo seochat tools logo