Can I make this MySQL staement with complex COUNTs more efficient?

Question

I have a table of all Major League Baseball games that is structured like this:

**Game**
id (int)
home_team_id (int)
away_team_id (int)
home_score (int)
away_score (int)
date (date)

I also have table named "ticket" that has tickets sold to different games:

**Ticket**
id (int)
game_id (int)
price (float)
time_sold (datetime)

I'd like to run a MySQL query that for each ticket listing matches up the home and away team's record at the time that the ticket was sold. I've been using this:

SELECT ticket.id, game.home_team_id AS Home, game.away_team_id AS Away,
(SELECT COUNT(game.id) FROM game WHERE game.date < DATE_SUB(ticket.time_sold, INTERVAL 1 DAY) AND (game.home_score > game.away_score AND game.home_team = Home OR game.away_score > game.home_score AND game.away_team = Away)) as home_team_wins
FROM ticket
JOIN game
ON game.id = ticket.game_id

The problem is that this query is extremely slow. I've indexed all of the columns in the game table and all except for price in the ticket table, but it's still painfully slow.

Can anyone suggest how I can speed this up?

Answer 1

I think the scalar select subquery in the SELECT clause might be the culprit. I would try moving it to the FROM clause.

Try this and see if it helps (I might have syntax errors, I don't have an environment to test today):

SELECT ticket.id
     , game.home_team_id AS Home
     , game.away_team_id AS Away
     , game_count
FROM (SELECT COUNT(game.id) as game_count FROM game WHERE game.date <  DATE_SUB(ticket.time_sold, INTERVAL 1 DAY AND (game.home_score > game.away_score AND game.home_team = Home OR game.away_score > game.home_score AND game.away_team = Away))) as home_team_wins
   ,ticket JOIN game ON game.id = ticket.game_id