Mysql Query optimisation
- or -
Post a project like this- Posted:
- Proposals: 13
- Remote
- #2135141
- Expired
Description
We run queries against these 2 tables. All of the queries are client and date specific, that means only certain number of rows are scanned. These have indexes on right columns. In the example attached Only 65,000 rows are scanned even though there are 50,000,000 rows in the table.
Still the query doesn't finish in 10 minutes.
I think the problem lies in the left join subquery.
If anyone can help optimising the query or recommend any other ways to make it faster please get in touch.
Any questions, let me know.
I attached the mysql query plan as well as the simplified sql query that needs optimizing.
Joy D.
0% (0)New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
Hi Joy D,
Have you solved performance issue or is still actual?
Thanks -
I agree, it looks terrible, 4 tables and 7 joins, could be simplified by using a temp table then querying that, joining back onto the main table if required. If you're doing any kind of sort or limit on this as well that wont help.
I can see where you need to be, does it all need to be in one mysql query? I've found it's sometimes faster to break things up, is the language running the query PHP? -
Hello Joy.
This SQL query is awful. Can you explain its goal
---
Thank you.
Alexey -
Sorry - that SQL has me confused. Can you explain in English what it is supposed to be doing. Thanks
Joy D.11 Sep 2018So we have a table sales.
Another table items.
A sale consists of many items.
Each item can be discounted by amount or percentage.
The overall sale can be discounted on top of that by amount of percentage.
To calculate the actual price of an item we need to get the total after item discount of all the items and then apply sales discount on top of it but distributing the discount amount across the items based on the price of that item relative to the total sale amount.
So, for example item A is £10 and 50% off so the price is £5.
Item B is £10 but no discount.
The total is £15. Now we apply a sales discount £5. So the total they pay is £10 and there is £5 discount for the sale. This discount will need to distributed based on the item amount. So one-third of the discount will go to item A and the rest to item B.
We don't store the calculated values on the table and do it on the fly hence the complicated subqueries.
Hope that makes sense.