Hi,

I have a table with hierarchical data, which is stored using the
Modified Preorder Tree Traversal algorithm (MPTT).

More information about MPTT can be found here:
http://dev.mysql.com/tech-resources/...ical-data.html.

I was wondering if indexing on the lft and rgt column will have speed
advantages, as the cardinality of the index will always be the same as
the number of rows there are in the database.

In my case, there are about 40000 rows in my hierarchical data table.

On Wed, 06 Feb 2008 12:25:55 +0100, <pieter.thoma@gmail.com> wrote:

> Hi,
>
> I have a table with hierarchical data, which is stored using the
> Modified Preorder Tree Traversal algorithm (MPTT).
>
> More information about MPTT can be found here:
> http://dev.mysql.com/tech-resources/...ical-data.html.
>
> I was wondering if indexing on the lft and rgt column will have speed
> advantages, as the cardinality of the index will always be the same as
> the number of rows there are in the database.

It will have advantages in selecting, it will make
inserting/altering/deleting slower.
--
Rik Wasmus

On Wed, 06 Feb 2008 12:31:30 +0100, Rik Wasmus
<luiheidsgoeroe@hotmail.com> wrote:

> On Wed, 06 Feb 2008 12:25:55 +0100, <pieter.thoma@gmail.com> wrote:
>
>> Hi,
>>
>> I have a table with hierarchical data, which is stored using the
>> Modified Preorder Tree Traversal algorithm (MPTT).
>>
>> More information about MPTT can be found here:
>> http://dev.mysql.com/tech-resources/...ical-data.html.
>>
>> I was wondering if indexing on the lft and rgt column will have speed
>> advantages, as the cardinality of the index will always be the same as
>> the number of rows there are in the database.
>
> It will have advantages in selecting, it will make
> inserting/altering/deleting slower.

BTW, one way to speed up inserting/deleting/updating, is to leave quite
huge gaps between lft & rgt values. This way, on almost all record
changes, no altering of other records (save for possible descendants) is
required (yet). This speeds up things a lot, but requires quite some
carefull code checking for gaps & need, and possibly a scheduled
'reindexing' at some regular interval.
--
Rik Wasmus

On Feb 6, 12:42 pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> On Wed, 06 Feb 2008 12:31:30 +0100, Rik Wasmus
>
>
>
> <luiheidsgoe...@hotmail.com> wrote:
> > On Wed, 06 Feb 2008 12:25:55 +0100, <pieter.th...@gmail.com> wrote:
>
> >> Hi,
>
> >> I have a table with hierarchical data, which is stored using the
> >> Modified Preorder Tree Traversal algorithm (MPTT).
>
> >> More information about MPTT can be found here:
> >>http://dev.mysql.com/tech-resources/...ical-data.html.
>
> >> I was wondering if indexing on the lft and rgt column will have speed
> >> advantages, as the cardinality of the index will always be the same as
> >> the number of rows there are in the database.
>
> > It will have advantages in selecting, it will make
> > inserting/altering/deleting slower.
>
> BTW, one way to speed up inserting/deleting/updating, is to leave quite
> huge gaps between lft & rgt values. This way, on almost all record
> changes, no altering of other records (save for possible descendants) is
> required (yet). This speeds up things a lot, but requires quite some
> carefull code checking for gaps & need, and possibly a scheduled
> 'reindexing' at some regular interval.
> --
> Rik Wasmus

Yes it will. Luckily in my case, the data is static. It's a write
once, read many.

But inserting is slow indeed.