CODEWITHSUNDEEP
mysql
davidaam
davidaam

Reputation: 449

mySQL - Do the operations and store in table or do the math every time I fetch the data

As simple as that, what's better in terms of performance? I'm doing some calculations based on user data input (simple arithmetic), should I do the operations and store the result in the database or do the operation each time I do the SELECT query?

Option 1: operate each time I fetch data from the table

SELECT 
  some_random_fields,
  salary,
  extra_days,
  extra_days * salary * 0.05 AS extra_income 
FROM 
  table 
WHERE 
  user_id = 'xxx'

Option 2: operate once, INSERT INTO table, fetch result without operating (extra column)

SELECT 
  some_random_fields,
  salary,
  extra_days,
  extra_income 
FROM 
  table 
WHERE 
  user_id = 'xxx'

Upvotes: 0

Views: 1081

Answers (1)

Ken White
Ken White

Reputation: 125708

The answer is that it depends.

In the case you've described, it would clearly be advantageous to do the calculations at the time they're needed, because the salary or percentage (0.05) can clearly change over time (people get raises or demotions, reduction in hours, etc., or the current economy calls for using 0.04 instead of 0.05), and it's better to calculate it as needed than to have to update the entire database to store new extra_income. The cost of the calculation (especially when limited in scope to a single user by the WHERE) is negligible compared to the accuracy of the calculation and the elimination of the need to remember to update all of the data when things change.

If the data is static (rarely or never changes), or you need to retain the values (for some historical reason, or for an audit trail), do the calculation up front and store it. The extra space used isn't typically an issue, and since the data is static there's no need to repeat the calculations every time you're doing a SELECT.

Upvotes: 1

Related Questions