[FIXED] MySQL: single column SUM(IF(cond,a,b)) vs muti-column Sum()

Issue

Have a legacy LAMP application that I'm in the process of converting over to the JVM.

The problem in question entails a scoring table with @250K records. Currently the "scoreType" column is represented as a tinyint, where 1 = goal, 2 = assist1, 3 = assist2.

For scoring leaders, to get goal/assist1/assist2 totals for each player, query snippet looks like:

SUM(IF(scoreType=1,1,0)) AS goals, SUM(IF(scoreType=2,1,IF(scoreType=3,1,0))) AS assists

Fair enough, does the job, but I'm wondering if, in terms of general schema design where query context will entail SUM, COUNT, etc. operations, it is better to break out limited choice sets like goal/assist1/assist2, win/loss/tie, etc. into separate columns rather than the tinyint approach?

In separate columns the query then becomes:

SUM(goal) AS goal, SUM(assist1) AS assist1, SUM(assist2) AS assist2

Which is a performance win (no need for if(cond,a,b) match) at the cost of slightly increased storage (3 columns vs. 1).

At the application layer one potential big win is moving from ORM non-supported SUM(if()) to column.Sum(); otherwise, I'll need to keep non-statically typed string SQL queries with the all-in-one-column approach

Thoughts? How would you address, leave as is, or migrate DB and application code to 3 column approach?

Thanks for feedback!

Solution

SELECT scoreType, COUNT(scoreType)
FROM ...
GROUP BY scoreType

Wouldn't it?

---

For an honest timing to compare the alternatives, use the more verbose;

SELECT COUNT(CASE WHEN scoreType = 1 THEN id ELSE NULL END) AS goals,
       ...

Answered By - Joop Eggen