Querying math operations vs using an update trigger on fixed column in mysql database

Question

I have a table with two numeric values and I will query the difference between those two values over thousands of entries. I have two alternatives:

1. To query the difference directly like SELECT (column_1 - column_2) as 'DIFFERENCE' FROM 'Table_Name'
2. To have an UPDATE Trigger that automatically updates a fixed 'difference' column so I can then just query the difference column directly

As I mentioned before I will be querying potentially tens of thousands of entries so maybe option 1 would represent some kind of overload while option 2 will perform the subtraction only when necessary.

I am no expert on database performance optimization though, so maybe there's something I'm missing that somebody with more experience could point out.

Thanks in advance.

Answer 1

An alternative would be a generated column e.g.

ALTER TABLE table_name ADD difference GENERATED ALWAYS AS (column_1 - column_2)

You can choose to add the STORED keyword to the end of the ALTER TABLE command to ensure that the value is computed once (on INSERT and UPDATE operations), or you can omit it (or add the VIRTUAL keyword) to indicate that the column should be computed when read. Using VIRTUAL is like your option 1; STORED is like your option 2.

Here's a small generated column demo on dbfiddle.