CODEWITHSUNDEEP
mysql
user1723535
user1723535

Reputation: 25

MySQL Query: Simplifying Subqueries and Union

I have a table with the following columns: match_id, player_slot, ability, level

There are 10 player slots per match and each hero can have up to 25 levels and one of five abilities. I'm pulling the data from an API and need the pulls to be fast so I don't have much flexibility with how my table is set up.

However for displaying the data, I'm really struggling to turn the existing table format into something that is workable. For each match, I'd like to have the data laid out like this:

player slot1, ability at level 1, ability at level 2, ..., ability at level 16

...

player slot10, ability at level 1, ability at level 2, ..., ability at level 16

I can get a working example ( Here's an example ) up but it's a really ugly query. For each row I have 15 nested subqueries and then perform a UNION to join each of the 10 rows.

Snippet of the query:

SELECT player_slot, ability,
(SELECT ability FROM api_abilities WHERE match_id = 119009486 AND player_slot = 1 AND level = 2 ),
...
(SELECT ability FROM api_abilities WHERE match_id = 119009486 AND player_slot = 1 AND level = 16 )
FROM api_abilities
WHERE match_id = 119009486 AND player_slot = 1 AND level = 1

How would I go about simplifying this? Should I make a view or a new table with this data presented in a different way? Also complicating this is that each player will end the game at different levels. What I have now does work but it seems that there has to be a more efficient way to run the query. I've tried a few different things but can't seem to get the row consolidations correct on my other queries.

Upvotes: 0

Views: 129

Answers (3)

spencer7593
spencer7593

Reputation: 108530

Here's one approach:

SELECT a.player_slot
     , MAX(IF(a.level=1,a.ability,NULL)) AS ability_at_level_1
     , MAX(IF(a.level=2,a.ability,NULL)) AS ability_at_level_2
     , MAX(IF(a.level=3,a.ability,NULL)) AS ability_at_level_3
     , MAX(IF(a.level=4,a.ability,NULL)) AS ability_at_level_4
     , MAX(IF(a.level=5,a.ability,NULL)) AS ability_at_level_5
   ...   
     , MAX(IF(a.level=15,a.ability,NULL)) AS ability_at_level_15
     , MAX(IF(a.level=16,a.ability,NULL)) AS ability_at_level_16
  FROM api_abilities a
WHERE a.match_id = 119009486
  AND a.player_slot >= 1
  AND a.player_slot <= 10
GROUP
   BY a.player_slot

If you wanted the query to return more than one match_id, you would want the GROUP BY clause to include the match_id column before player_slot, (as well as including it in the SELECT list.

GROUP
   BY a.match_id
    , a.player_slot

For best performance, you'll want a suitable index. A covering index would (likely) give the best query performance:

ON api_abilities (match_id, player_slot, level, ability)

The reason this is a suitable index is because you have an equality predicate on the match_id column, and a range predicate and a GROUP BY on the player_slot column. (This index is still suitable even if the range predicate on player_slot is removed.) The inclusion of the level and ability columns aren't strictly necessary, but including them makes the index a "covering index". It's called a "covering index" because the query can be satisfied entirely from the index (the EXPLAIN output will show "Using index") without needing to visit the data page underlying the index.

The use of the MAX aggregate and the IF function is what allows us to "pick out" the a.ability from the appropriate row. (The "trick" is that for any row that isn't at the specified level, the IF function is returning a NULL. What we are really doing with the MAX is throwing out those NULL values.

If (match_id, player_slot, level) is unique, then that MAX aggregate function will be operating on one a.ability value and a bunch of NULLs. In the more general case, if we don't have that guaranteed to be unique, then there's the possibility that the MAX aggregate would be operating on two (or more) non-NULL a.ability values. In that more general case, a GROUP_CONCAT aggregate function might be useful (in place of the MAX) if you wanted to return a short list of a.ability (as a string) at each level.

Upvotes: 1

Taryn
Taryn

Reputation: 247880

This type of transformation is a pivot. Unfortunately, MySQL does not have a pivot function so you will need to replicate it using multiple joins or aggregate functions.

You can use multiple joins instead of the subqueries:

SELECT 
  l1.player_slot, 
  l1.ability Level1Ability,
  l2.ability Level2Ability,
  l16.ability Level16Ability
FROM api_abilities l1
LEFT JOIN api_abilities l2
  on l1.match_id = l2.match_id 
  and l1.player_slot = l2.player_slot
  and l2.level = 2
-- .... add more joins for each level
LEFT JOIN api_abilities l16
  on l1.match_id = l16.match_id 
  and l1.player_slot = l16.player_slot
  and l16.level = 16
WHERE l1.match_id = 119009486 
  AND l1.player_slot >= 1
  AND l1.player_slot <= 10
  AND l1.level = 1

Or you can use an aggregate function with a CASE expression:

select
  player_slot,
  max(case when level = 1 then ability end) Level1Ability,
  max(case when level = 2 then ability end) Level2Ability,
  -- ... add more case expressions for each level
FROM api_abilities 
where match_id = 119009486 
  and player_slot >= 1
  and player_slot <= 10
group by player_shot

Upvotes: 1

Mike Brant
Mike Brant

Reputation: 71422

It sounds to me like you need to get a better understanding of database normalization. I'm still not exactly sure what you are trying to do here based on your description, but it seems clear to me that this data should be represented across multiple tables (one for matches, one for players, one for abilities, one relating players to abilities and ability levels, one relating players to matches, etc.).

Upvotes: 0

Related Questions