I have a slightly complex SELECT query which uses a number of JOINs and variables inside it and the query is run frequently throughout the application.
The result varies when one column is updated. Eg: When the product quantity is updated only, the result set changes.
So my question is, is it a good idea to run a trigger and update a temporary table in the database, with my SELECT queries results, whenever the product quantity is updated? Or to run the complex SELECT query every time?
Right now I tested with ~10 rows of data and the query execution time is 0.001 seconds. I want to use the best possible method which won't hinder the performance as the DB grows.