Some Serious SQL Query Optimization Needed (MySQL)

Here's my current query:

SELECT IFNULL(sum(open_for), 0) total, count(IF(open_for > 0, 1, null)) wins, count(IF(open_for < 0, 1, null)) losses FROM `sport_points` WHERE (sportable_id = 1 and sportable_type = 'Team' and game_time > '2010-07-13 11:39:58 UTC'

It basically returns this aggregated data:

TEAM A

• open_for
• total: 2000
• wins: 20
• losses: 12

Now, imagine there are about 6 other columns in the table that I need execute separate queries to get all the column-specific aggregate data for one team. For example:

SELECT IFNULL(sum(FINAL_FOR), 0) total, count(IF(open_for > 0, 1, null)) wins, count(IF(open_for < 0, 1, null)) losses FROM `sport_points` WHERE (sportable_id = 1 and sportable_type = 'Team' and game_time > '2010-07-13 11:39:58 UTC'

TEAM A

• final_for
• total: 4000
• wins: 40
• losses: 18

The problem with this approach is that I have to run about 6 separate queries for all the columns on well over 200 teams. It's a serious load problem.

Ideally, the query would return all the column-specific aggregate data for one team -- in one query. It would look like this in the result:

TEAM A

• open_for_total
• open_for_wins
• open_for_losses
• final_for_total
• final_for_wins
• final_for_losses

...开发者_运维技巧etc...

Just for the benefit of those who want to help:

Query 1:

SELECT
    IFNULL(sum(open_for), 0) total
    ,COUNT(IF(open_for > 0, 1, null)) wins
    ,COUNT(IF(open_for < 0, 1, null)) losses
FROM
    `sport_points`
WHERE
    sportable_id = 1
    AND sportable_type = 'Team'
    AND game_time > '2010-07-13 11:39:58 UTC'

Query 2:

SELECT
    IFNULL(SUM(FINAL_FOR), 0) total
    ,COUNT(IF(open_for > 0, 1, null)) wins
    ,COUNT(IF(open_for < 0, 1, null)) losses
FROM
    `sport_points`
WHERE 
    sportable_id = 1
    AND sportable_type = 'Team'
    AND game_time > '2010-07-13 11:39:58 UTC'

Desired output columns: teamname, typeofquery, value

Where typeofquery is one of:

• open_for_total
• open_for_wins
• open_for_losses
• final_for_total
• final_for_wins
• final_for_losses

derived from the two columns open_for and final_for in conjunction with the wins and losses columns.

Initially thinking about the problem I'm guessing an intermediate table might help processed with a GROUP BY clause.

e.g.

INSERT INTO
  temptable
SELECT
  teamname
  ,'open_for' type
  ,IFNULL(SUM(open_for), 0) total
  ,COUNT(IF(open_for > 0, 1, null)) wins
  ,COUNT(IF(open_for < 0, 1, null)) losses
FROM
  `sport_points`
WHERE
  sportable_id = 1
  AND sportable_type = 'Team'
  AND game_time > '...'
GROUP BY
  teamname

then run the same query but summing final_for. Now your temp table contains rows like:

teamname, type, total, wins, losses
TEAM A, open_for, 100, 37, 63
TEAM A, final_for, 30, 10, 20
TEAM B, open_for, 12, 8, 4
TEAM B, final_for, 50, 49, 1

Your final query could just concatenate the columns as desired.

I'd do this as a single query which returns separate columns for each of the statistics. I'd then restructure the results in my application code if necessary. The query looks something like:

select teamname,
 sum(open_for) as open_total,
 count(if(open_for > 0, 1, null)) as open_wins,
 count(if(open_for < 0, 1, null)) as open_losses,
 sum(final_for) as final_total,
 count(if(final_for > 0, 1, null)) as final_wins,
 count(if(final_for < 0, 1, null)) as final_losses,
from sport_points
where sportable_id = 1
 and sportable_type = 'Team'
 and game_time > '...'
group by teamname

This, i suggest, is a more orthodox relational approach, and so one which is easily expressed in SQL. If it's not quite what you need in your application, then the place to make the adjustment is in code, which is far more flexible than SQL.