MySQL for selecting MAXIMUM differences of two columns

user_6337701 0
Viewed 300

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

mysql php
2 Answers

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.

user_35229570

The first method is fastest, but is prone to error, as was mentioned.

May I suggest another solution, using a primary key. You could then set the value of the new column to what you are trying to figure from within the web application.

Then, when wanting to know the top 50, you could use your original method of finding the top 50, using your second method, where you would select from the table which stores the differences. These links explain primary keys and how to use them:

http://www.mysqltutorial.org/mysql-primary-key/

https://www.w3schools.com/sql/sql_primarykey.asp

user_108157400
Related