optimization - MySQL: Optimizing queries

Question

How can I write this in a more optimized way?

SELECT week_day
     , SUM(min_0 + min_1 + min_2 + min_3) 
     / (SELECT SUM(min_0 + min_1 + min_2 + min_3)
             FROM Hotel.RegIn) * 100 AS MIN_PERCENTAGE
  FROM Hotel.RegIn 
 WHERE week_day = "Wednesday" 
 GROUP 
    BY week_day;

question from:https://stackoverflow.com/questions/65642834/mysql-optimizing-queries

Answer 1

I'd write it as below:

SELECT
    "Wednesday",
     100 * SUM((week_day = "Wednesday") * (min_0 + min_1 + min_2 + min_3))
        / SUM(min_0 + min_1 + min_2 + min_3) AS MIN_PERCENTAGE
 FROM Hotel.RegIn
;

or if you can use multiple statements, another one with easier filters:

SET @var_all_day_total :=
    (SELECT SUM(min_0 + min_1 + min_2 + min_3) FROM Hotel.RegIn) / 100;

SELECT
    week_day,
    SUM(min_0 + min_1 + min_2 + min_3) / @var_all_day_total AS MIN_PERCENTAGE
FROM Hotel.RegIn
-- WHERE week_day = "Wednesday"
GROUP BY
    week_day
;

The idea usually is not to redundantly calculate the same values more than once.