So I have a table of hierarchical data a la:
id INT NOT NULL UNIQUE,
The nested set model (lft,rgt fields) allows me to discern children and parents and depths and all kinds of greatness that I couldn't get from adjacency pairs (ie parent_id field).
However, I now have related data in another table like this:
Where the widget id is the widget's id and periods are like march 2013 etc (another table). My problem is I would like to roll up the costs in a given period to the parents, something like:
SELECT p.id, a.period_id, SUM(a.value) FROM Widget c INNER JOIN WidgetActuals a ON (a.widget_id = c.id) INNER JOIN Widget p ON (c.lft BETWEEN p.lft AND p.rgt) GROUP BY p.id, a.period_id;
would seem to get it done, but it's too slow.
In my case I have a Widgets table of about 10k entries, and the WidgetActuals contains about 70k entries, the above query takes ~5 minutes. I'd prefer to get it down to the speed of light if possible...Any help?