Problems with SELECT Runtime
Posted by:
mfsamuel
Date: February 14, 2005 03:41PM
Ok, here is the problem...
I am working with 5.0.2-alpha.
The query is:
SELECT gamerTag.gamertag, SUM(h2_players.score), SUM(h2_players.kills), SUM(h2_players.deaths), SUM(h2_players.assists), AVG(h2_players.score), AVG(h2_players.kills), AVG(h2_players.deaths), AVG(h2_players.assists), COUNT(h2_players.gameID), SUM(h2_players.kills)/SUM(h2_players.deaths), SQ_Plots.Plots
FROM h2_players
INNER JOIN gamerTag ON (gamerTag.gamertag=h2_players.gamertag)
INNER JOIN h2_games ON (h2_games.gameID=h2_players.gameID)
LEFT JOIN (SELECT STRAIGHT_JOIN h2_players.gamertag, SUM(h2_players.score) AS 'Plots'
FROM gamerTag
INNER JOIN h2_players ON (gamerTag.gamertag=h2_players.gamertag)
INNER JOIN h2_games ON (h2_games.gameID=h2_players.gameID)
WHERE h2_games.gameType IN ('3 Plots','Land Grab','Control Issue')
GROUP BY h2_players.gamertag) AS SQ_Plots ON (SQ_Plots.gamertag=h2_players.gamertag)
WHERE (h2_games.meet NOT IN ('Arranged Game','Team Training')
GROUP BY gamerTag.gamertag
gamerTag - stores account info; 4,367 Rows; 884.6 KB
h2_games - stores game info; 360,358 Rows; 50.5 MB
h2_players - stores player info for each game; 3,005,415 Rows; 190.3 MB
This query takes 1000+ seconds and locks all the database. If I run the subquery alone it takes ~10sec. The main query without the subquery takes about the same time. I would like to run this as an INSERT or REPLACE but the time constraints are too high.
I am unsure if this is a bug or simply a configuration/query problem, so some advice would be appreciated.