CODEWITHSUNDEEP
mysqloptimizationquery-optimization
tootiefairy
tootiefairy

Reputation: 7

MySQL: Optimizing queries

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;

Upvotes: 0

Views: 59

Answers (2)

Nae
Nae

Reputation: 15355

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.

Upvotes: 1

user14967413
user14967413

Reputation: 1416

You should use derived table in the FROM clause. This way the subquery will be evaluated only once.

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

Note that as far as you restrict your query only to one day of week, you won't get any performance gain by rewriting the query, because the subquery will be evaluated only once in both cases.

See CTE (Common Table Expressions) for more readable syntax of derived tables in newer versions of MySQL.

Upvotes: 1

Related Questions