CODEWITHSUNDEEP
mysqldatabase-designindexing
Krrish Raj
Krrish Raj

Reputation: 1535

Creating clustered index in mysql for hierarchical table

I have a hierarchical relation without any limited depth, every row will have a parent row specified in a column.
Most of the queries will be against that parent key only. So I am thinking to declare parent field as clustered index so that my queries can run fast. But the problem is table will have frequent insert and delete operations.
As far I know clustered index fields are sorted and point to data location on disk and all non-clustered index uses reference from clustered index only. So frequent insert operations should be costly I guess. So what should I do ? Is it fine to declare parent field as clustered index ?

Upvotes: 2

Views: 122

Answers (1)

Rick James
Rick James

Reputation: 142528

The PRIMARY KEY is the only "clustered" index available in MySQL. But the PK is UNIQUE. So, parent_id cannot be the clustered index, at least not by itself...

PRIMARY KEY(parent_id, id) would work. You get the "clustering" effect of having parent_id as the first part, and you get UNIQUE by including the id. I am assuming id would otherwise be the PK for the table, and each "item" is uniquely identified by id.

But wait, you declared id to be AUTO_INCREMENT? Then add INDEX(id); that will suffice.

To find all the 'children' of $parent:

SELECT ... WHERE parent_id = $parent ...

All the children will be "clustered next to each other" in the table, thereby efficient to fetch. The grandchildren, on the other hand will be elsewhere.

To find one's parent:

SELECT parent_id FROM tbl WHERE id = $me

To get info about one's parent, do a "self join":

SELECT p.*
    FROM tbl c
    JOIN tbl p  ON p.id = c.parent_id
    WHERE c.id = $me

To derive all the ancestors or all the descendants, it is probably best to use application code with a loop doing enough SELECTs to traverse the tree upward or downward.

Upvotes: 1

Related Questions