Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2003
    Posts
    12

    Unhappy Unanswered: Very Large Query Bring down our server.

    Hello,

    I created a stats program, what happens is our Statistician puts all of his infromation into an MS Access database, and he uses a program to convert the access database into an SQL file. He then uploads the SQL file to the server, and I run a query on a table, that contains over 10 thousand recoards. When we do this MySQL will crash sometimes. I will paste the query here.

    Code:
    CREATE TABLE tmp_player_2 AS SELECT `Players`.`PID`, sum(`Players`.`GP`) AS `SumOfGamesPlayed`, sum(`Players`.`G`) AS `SumOfGoals`, sum(`Players`.`A`) AS `SumOfAssists`, sum(`Players`.`PIM`) AS `SumOfPIM`, sum(`Players`.`PTS`) AS `SumOfPTS`, sum(`Players`.`PPG`) AS `SumOfPowerPlayGoals`, sum(`Players`.`SHG`) AS `ShortHandedGoals`, sum(`Players`.`ENG`) AS `SumOfEmptyNetGoals`, sum(`Players`.`GW`) AS `SumOfGameWinner`, sum(`Players`.`TG`) AS `SumOfTG`, sum(`Players`.`IG`) AS `SumOfIG`, sum(`G`) / sum(`GP`) AS `SumOfGoalsPerGame`, sum(`PTS`) / sum(`GP`) AS `SumOfPointsPerGame`, sum(`PIM`) / sum(`GP`) AS `SumOfPIMPG`, `Player_IDS`.`Pos`, `Player_IDS`.`No`, `Player_IDS`.`TID` As TID, `Player_IDS`.`Name`, `Player_IDS`.`Rookie`, `Team_IDS`.`Team` FROM `Players` INNER JOIN `Team_IDS` ON (`Player_IDS`.`TID` = `Team_IDS`.`TID`) INNER JOIN `Player_IDS` ON (`Players`.`PID` = `Player_IDS`.`PID`) INNER JOIN `Games` ON (`Players`.`Game` = `Games`.`Game_ID`) WHERE (`Games`.`Type` = 2) GROUP BY `Players`.`PID
    What would u recomend me do to this query that will make it work better? Also this query is ran 6 times total, with a rest of 20 seconds between all of them, but it will only crash on the 10 thounsand recoard table.

    We are running MySQL 4.0.23-standard. Our server is a 3GHz, with 1GB of ram.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do you have indexes defined for the columns being used in the joins?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2003
    Posts
    12
    Hello,

    Thanks for the fast reply.

    I just checked and the only feild that has a Key is the Players Table (with 10 thousand recoards) and that is on the ID feild, witch does not get used in the query.

    Thanks,
    Jeremy Ross

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in that case, how about creating some indexes, and running your query again
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2003
    Posts
    12
    Hello,

    Thanks for the input. So adding index's to the tables would make the processing go faster? I don't want to make any trouble by chaning to much about the database. Just adding indexes to the ID's wouldn't be hard. I dont' want to add too many indexes to each table because that would slow it down, correct?

    Thanks,
    Jeremy

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, each index makes SELECTs much faster but INSERTs UPDATEs and DELETEs a tiny bit slower

    so do the calculation, multiply out how many times per day that each type is executed, times either much or tiny
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Posts
    10
    Does MySQL support a bulk insert feature similiar to Microsoft SQL?

    If not, any plans for it in the 5.x releases?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2003
    Posts
    12
    Thanks for all your help, I very much appreciate it! The database is now working great!

    Thanks so much
    Jeremy Ross

Posting Permissions

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