EXPLAIN EXTENDED

How to create fast database queries

Archive for March 21st, 2009

Hierarchical queries in MySQL: finding loops

with 4 comments

Today, we will check our structure for loops.

Loops in hierarchical queries occur when a row contains itself in its ancestry chain.

In general, it's a sign of a database logic flaw and should be avioded, but sometimes it's deliberately used.

The simplest and most easily detectable case is having id equal to parent (meaning that the row is both its father and child). This may be tested by using a simple comparison condition.

But a loop of course can be more complex: a row can be its own grandfather, grand-grandfather etc.

To detect such loops, we will improve our functions a little.
Read the rest of this entry »

Written by Quassnoi

March 21st, 2009 at 11:00 pm

Posted in MySQL