NYCPHP Meetup

[csnyder]

On 10/27/05, Mitch Pirtle <mitch.pirtle at gmail.com> wrote:
> On 10/27/05, Hans Zaunere <lists at zaunere.com> wrote:
> >
> > Ahh, good old NSM...
> >
> > New York PHP developed an implementation of NSM, pNSM, as part of the clew
> > project.
>
> Then let's start that discussion over here, about the performance
> benefits that turn into performance hits when your one table has 300
> million rows...
>
> Seriously, this concept makes lots of things seem easier, but next
> thing you know, you got a really looooooong table on your hands and
> only Oracle (IIRC) can partition across tablespaces at the table
> level.
>
> What to do when your nested table becomes more than you can manage? Is
> it time then to go back to a more normalized model, or is this an
> indication that - before you even implement - you have to take the
> eventual number of rows that your nested table might have to manage?
>
> -- Mitch, loving database discussions for a change ;-)

We were having just this discussion at Friday's after yiou left,
Mitch, only my number was much lower than 300 million. The overhead of
inserting new nodes into the tree becomes unbearable much more quickly
than I expected. On commodity hardware with a stock MySQL binary, it
can take four seconds to insert a new node in a tree of 50,000.

Then again, for many projects the convenience more than makes up for
the insertion overhead, and the read performance on the NSM tree
certainly scales as advertised.

But what's the answer? You can finesse the problem a bit, depending on
your data model, by using sub-trees in their own tables. Or you can
use Oracle. Or... ?

--
Chris Snyder
http://chxo.com/