MySQL for selecting MAXIMUM differences of two columns

Question

I have a table with following columns:

ItemCode VARCHAR
PriceA DECIMAL(10,4)
PriceB DECIMAL(10,4)

The table has around 1,000 rows.

My requirement is to check the difference (PriceA-PriceB) for each row and then display top 50 items that have maximum price differences.

There are two ways I can implement this

1) Trust that SQL calculation is non-complex, easy and fast, so I run the following query:

SELECT ItemCode, (PriceA - PriceB) AS PDiff  FROM testtable  ORDER BY PDiff DESC LIMIT 50

and second,

2) Add one more column (called PriceDiff), which will store the difference (PriceA-PriceB).

However, these will have to be inserted manually and need extra space. But it can simply run the MAX(PriceDiff) select query for top 50.

My question is - in terms of speed and efficiency for a web application (displaying results on a website/app), which of the above method is better?

I have attempted to generate time consumed for each query, but both are reporting similar figures so unable to make any inferences.

Any explanation by the experts, or any fine-tuning of code, will be really appreciated.

Thanks

Answer 1

In general, to improve performance you always have to make a tradeoff between memory and time. Caching results will improve speed, however takes more memory. You can reduce memory usage by calculating stuff on the fly at the expense of performance.

In your case, storing additional 1000+ values in the DB is a matter of few extra Kb. Calculating the diff on the fly will have a negligible impact on performance. Either option is absolute peanuts to any DB and server.

I would stick with doing calculations on the fly as that is less complex and keeps the db normalized.