Very Large Query Bring down our server.

Jeremy Ross · #1 (**permalink**) 02-04-05, 09:03

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.

r937 · #2 (**permalink**) 02-04-05, 09:10

do you have indexes defined for the columns being used in the joins?

Jeremy Ross · #3 (**permalink**) 02-04-05, 09:31

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

r937 · #4 (**permalink**) 02-04-05, 09:55

in that case, how about creating some indexes, and running your query again

Jeremy Ross · #5 (**permalink**) 02-04-05, 13:14

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

r937 · #6 (**permalink**) 02-04-05, 14:28

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