Suggested Indexes for the MPTT Table

Question

I just create a table to store hierarchical data using a modified pre-trading order tree (MPTT) - you know one thing: each node stores identifiers leftand right, to find its descendants, I use the proposed CakePHP model, which varies from the standard way, including parent_idwith each row .

The table structure is suggested here:

CREATE TABLE categories (
    id INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    parent_id INTEGER(10) DEFAULT NULL,
    lft INTEGER(10) DEFAULT NULL,
    rght INTEGER(10) DEFAULT NULL,
    name VARCHAR(255) DEFAULT '',
    PRIMARY KEY  (id)
);

I have never used this style before and without knowing exactly how to look for it, I wonder what fields should I index? It is only the main key enough, or should I include lftand rghttoo?

Answer 1

i . mysql, , , - MPTT, .

, - / .

Answer 2

, .

WHERE lft = (rgt -1)

lft, rgt.