• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 85
  • Last Modified:

MYSQL aggregates taking forever for nested-set-modeled data

So I have a table of hierarchical data a la:

Widgets:
id INT NOT NULL UNIQUE,
lft INT,
rgt INT,
depth INT

Open in new window


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:

WidgetActuals:
widget_id INT,
cost DECIMAL,
period_id INT

Open in new window


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;

Open in new window



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?
0
FLEA2007
Asked:
FLEA2007
1 Solution
 
ste5anSenior DeveloperCommented:
As you're roll up the entire tree, this will take a while.

I would consider using an additional path column in your structure, then the grouping can be done on the path.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now