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