MySQL Forums
Forum List  »  Newbie

Help with table query efficiency
Posted by: Paul Brittain
Date: May 25, 2017 06:27AM

Hi

I have a large MySQL database that I am querying and I am having a problem that one specific query is very slow to execute. I'm wondering if there is a better way to implement the query? Below is a simplified example of the tables and what I'm trying to achieve.

tblSales
SaleID SaleDate refProductCode Qty
1 2017/01/05 1 3
2 2017/03/17 1 2
3 2017/03/25 2 5
4 2017/04/10 1 3

tblProductPrice
PriceID ProductCode Price EffectiveDate
1 1 10 2017/01/01
2 2 20 2017/01/01
3 1 11 2017/03/01
4 1 12 2017/04/01
5 3 30 2017/01/01
6 2 21 2017/03/01
7 4 40 2017/01/01

The (simplified) output from the query is all the records from the sales table, but with a lookup Price and calculated total:

SaleDate ProductCode Qty Price Total
2017/01/05 1 3 10 30
2017/03/17 1 2 11 22
2017/03/25 2 5 21 105
2017/04/10 1 3 12 36

The tricky part is that in order to look up the price in tblProductPrice, one needs to find the largest effective date for the product that is less than the sale date. I didn't set up the original database and don't think this structure is great, but I can't change it as the database is used on another software platform.

My current implementation joins to tblProductPrice on the product code and checks that the EffectiveDate is in a result of a subquery which select the maximum EffectiveDate for a product with the date <= SaleDate. I'm getting the correct return from the query, but it takes long to run (we're talking a few hundred thousand records in tblSales).

What would be the most efficient way to achieve this query?

Thanks is advance.

P.S. Sorry for the formatting of the above. I don't know how to insert a table in the editor.

Paul

Options: ReplyQuote


Subject
Written By
Posted
Help with table query efficiency
May 25, 2017 06:27AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.