Complex query taking too long to run

I have a query (original question that has table structure too) that is perfect, but it creates a temporary table which unfortunately takes like 12 seconds due to amount of data. (1 table has 95k records, another 155k, and another 21k).

Is there any way to go around the temporary table solution or make it run faster? Maybe suggest which fields should be indexed? I have ID fields, date fields, etc... indexed, but that's not helping at all.

SELECT
    (
        CASE
            WHEN a.winner = a.f_a THEN "Win"
            WHEN a.winner = a.f_b THEN "Loss"
            WHEN a.winner IS NULL THEN a.method
        END
    ) AS result,
    SUM(a.f_a IN (d.fighter_a, d.fighter_b) AND d.winner <=> a.f_a) AS fighter_wincount,
    SUM(a.f_a IN (d.fighter_a, d.fighter_b) AND d.winner IS NOT NULL AND d.winner <> a.f_a) AS fighter_losscount,
    SUM(a.f_a IN (d.fighter_a, d.fighter_b) AND d.method = "Draw") AS fighter_drawcount,
    SUM(a.f_a IN (d.fighter_a, d.fighter_b) AND d.method = "No Contest") AS fighter_nocontestcount,
    b.name AS opponent,
    SUM(a.f_b IN (d.fighter_a, d.fighter_b) AND d.winner <=> a.f_b) AS opponent_wincount,
    SUM(a.f_b IN (d.fighter_a, d.fighter_b) AND d.winner IS NOT NULL AND d.winner <> a.f_b) AS opponent_losscount,
    SUM(a.f_b IN (d.fighter_a, d.fighter_b) AND d.method = "Draw") AS opponent_drawcount,
    SUM(a.f_b IN (d.fighter_a, d.fighter_b) AND d.method = "No Contest") AS opponent_nocontestcount,
    b.fighter_id AS opponent_id,
    b.fighting_out_of_country AS opponent_country,
    a.method AS method,
    a.method_type AS method_type,
    a.round AS round,
    a.time AS time,
    c.event_id AS event_id,
    c.event_name AS event,
    c.event_date AS date,
    c.event_city AS event_city,
    c.event_state AS event_state,
    c.event_country AS event_country
FROM
    (
        SELECT 
            fight_id,
            IF(fighter_b = :fighter_id_3, fighter_b, fighter_a) AS f_a,
            IF(fighter_b = :fighter_id_4, fighter_a, fighter_b) AS f_b,
            winner,
            method,
            method_type,
            round,
            time,
            event
        FROM 
            fights
        WHERE
            :fighter_id_5 IN (fighter_a, fighter_b)
    ) a
INNER JOIN
    fighters b ON a.f_b = b.fighter_id
INNER JOIN
    events c ON a.event = c.event_id
LEFT JOIN
    (
        SELECT 
            a.fighter_a,
            a.fighter_b,
            a.winner,
            a.method,
            b.event_date
        FROM
            fights a
        INNER JOIN
            events b ON a.event = b.event_id
    ) d ON 
        (a.f_a IN (d.fighter_a, d.fighter_b) OR a.f_b IN (d.fighter_a, d.fighter_b)) AND
        d.event_date <= c.event_date
GROUP BY
    a.fight_id
ORDER BY
    date DESC

Solution

Using a.id IN (b.a_id, b.b_id) is never going to be performant, you won't be able to create an index to fit the query. You should normalise that aspect of your structure.

I propose the following changes to your schema:

Add fight_date_time to your fights table
Move fighter_a and fighter_b out to a participicants table
- fight_id, fighter_id - Normalised - Two rows per fight

The following will create two records for every fight (One for each fighter), but will also list their opponent, and both their records going in to the fight.

SELECT
  event.event_name,
  event.event_date,

  MAX(CASE WHEN participant.fighter_id = fighter.id THEN fighter.name   END) AS fighter,
  MAX(CASE WHEN participant.fighter_id = fighter.id THEN record.wins    END) AS wins,
  MAX(CASE WHEN participant.fighter_id = fighter.id THEN record.draws   END) AS draws,
  MAX(CASE WHEN participant.fighter_id = fighter.id THEN record.losses  END) AS losses,

  CASE WHEN fight.winner = participant.fighter_id THEN 'Win'
       WHEN fight.winner IS NULL                  THEN 'Draw'
                                                  ELSE 'Loss' END            AS result,
  fight.method,

  MAX(CASE WHEN participant.fighter_id <> fighter.id THEN fighter.name  END) AS Opp,
  MAX(CASE WHEN participant.fighter_id <> fighter.id THEN record.wins   END) AS Opp_wins,
  MAX(CASE WHEN participant.fighter_id <> fighter.id THEN record.draws  END) AS Opp_draws,
  MAX(CASE WHEN participant.fighter_id <> fighter.id THEN record.losses END) AS Opp_losses      
FROM
  event
INNER JOIN
  fight
     ON event.id = fight.event_id
INNER JOIN
  participant
    ON participant.fight_id = fight.id
INNER JOIN
(
  SELECT
    participant.fighter_id,
    participant.fight_id,
    SUM(CASE WHEN prevFight.winner  = participant.fighter_id THEN 1 ELSE 0 END) AS wins,
    SUM(CASE WHEN prevFight.winner IS NULL)                                     AS draws,
    SUM(CASE WHEN prevFight.winner <> participant.fighter_id THEN 1 ELSE 0 END) AS losses
  FROM
    participant
  INNER JOIN
    fight
      ON  participant.fight_id = fight.id
  INNER JOIN
    participant        AS prevParticipant
      ON  prevParticipant.fighter_id = participant.fighter_id
  INNER JOIN
    fight              AS prevFight
      ON  prevFight.id              = prevParticipant.fight_id
      AND prevFight.fight_date_time < fight.fight_date_time
  GROUP BY
    participant.fighter_id,
    participant.fight_id
)
  AS record
    ON record.fight_id = participant.fight_id
INNER JOIN
  fighter
    ON fighter.id = record.fighter_id
GROUP BY
  event.id,
  fight.id,
  participant.fighter_id

If you want to see the results for just one fighter, add:
- WHERE participant.fighter_id = :fighter_id

Even better, keep this kind of data up to date with triggers. That way you don't need to calculate it again and again and again.