I have two counters in a mysql table. I would like to have another column that contains the difference between counter column 1 and counter column 2. Could I use a trigger after counter column 2 is updated which will subtract the number in counter 2 from the number in counter 1 and put the results in count column 3? I would want to index count column 3 for queries.
You can use a generated column in your
CREATE TABLE statement to always have an up-to-date value:
CREATE TABLE TestTable( id INT AUTO_INCREMENT PRIMARY KEY, col1 INT NOT NULL, col2 INT NOT NULL, col3 INT AS (col1 - col2) );
The column can be virtual or stored (virtual is default) and each has their own use cases as detailed in the link above.
Note: only applicable for MySQL 5.7.6 & higher.