How to calculate the sum of children of the required node for each day of the month (Nested Set)?

Question

PHP

Good afternoon.
Given: There is a hierarchical structure of the Nested Set type, 6 levels - table structure. The sales table, where sales are entered every day for the lower levels of the hierarchy (those who do not have "children"). Fields: structure_id, date, sum.
I need to do the following: When requested, I must get the sum of sales for each day of the current month for any node. Those. I need to get the sum for the node with id = 6. Using a query, I have to pull out the structure from the database and calculate the sales for each day of the month (ie, the maximum is 31).
I made the required request, but it takes 6 seconds, which is not allowed. I need the fastest request.
Of course, my knowledge of SQL syntax is quite primitive, so I turn to experts.

Answer 1

Study GROUP BY, which in the case of MySQL will carefully sort everything, group it, and calculate the amount for the company.
I don’t understand where it came from and how getTreeSum() works, it seems to me that some kind of extra overkill can occur inside.
Next - add EXPLAIN before your query and show the result - this will tell you where everything is bad.
I suspect that the query should be divided into 2 parts - displaying the ids of child nodes by a nested subquery and SUMm on the received:
WHERE id IN (select .... left_key >= left_key_of_the_required_node AND right_key <= right_key_of_the_required_node)

Answer 2

that is, a query by type takes 6 seconds? What query are you using, what is the structure of the structure table? select * from structure where parent_id = 6

Answer 3

ThunderCat
table structure
fields: id, name, left_key, right_key, level
When I need to calculate monthly sales, I use a query

SELECT *, (SELECT getTreeSum(left_key, right_key, startDate, endDate)) as sum FROM structure
LEFT JOIN sales ON structure.id = sales.structure_id
WHERE left_key >= левый_ключ_необходимого_узла AND right_key <= правый_ключ_необходимого_узла

and everything works, though two requests are made, but this is not so critical. getTreeSum is my function which counts the sum of all child nodes for a certain period startDate - endDate.
But if you do it by analogy for each day, then you get a long request (because for each day the parameters will be startDate = endDate and the request will be executed for a long time, because it turns out requests 1 + number_of_days_in_month. That is, at least 28 requests! So I want find faster query