Mysql adjacency list model alternative?

Question

I used the adjacency list model for my table in MySQL, along with a PHP function recursion to query and realised it slows down the website. Is there an alternative model i could use that i can use one query to get both parent and child elements, without recursion?

The table structure is like this:

    myTable:
+----+----------+
| id | parentID |
+----+----------+
|  1 |     null |
|  2 |        1 |
|  3 |        1 |
|  4 |        2 |
|  5 |        4 |
-----------------

I'm using a recursion query like below:

function  queryf($id) {

$query = mysql_query("SELECT * FROM `Table` WHERE id='$id'",$this->connect);
while($row = mysql_fetch_assoc($query)) {

$sid = $row['id'];
//code
$this->$queryf($sid);
}

}

You have to let us know how you are "using recursion" to get things done, so that you can get more helpful answers — Adriano Carneiro, Aug 23 '11 at 15:49
possible duplicate of [recursive self query](http://stackoverflow.com/questions/3276136/recursive-self-query) — nikc.org, Aug 23 '11 at 15:58
possible duplicate of [MySQL - Best method to handle this hierarchical data?](http://stackoverflow.com/questions/3137674/mysql-best-method-to-handle-this-hierarchical-data) — outis, Sep 09 '11 at 12:09

score 2 · Answer 1 · edited May 23 '17 at 12:26

2

You can use nested sets approach.

See more info in http://en.wikipedia.org/wiki/Nested_set_model and Move node in nested set

edited May 23 '17 at 12:26

Community

1
1

answered Aug 23 '11 at 15:52

Andrej

7,474
1
19
21

do i need multiple tables in MySQL for this model?.. because i only want to use one table. – user892134 Aug 23 '11 at 16:04

score 2 · Answer 2 · answered Sep 09 '11 at 11:58

How many records are you storing in your table? Nested sets are definitely faster but the table structure is very delicate. If it breaks it is very challenging to fix the table structure and it generally requires manual intervention.

If you only have a few hundred records, you are probably better off with the Adjacency method. If you are talking about thousands or records, definitely better off with nested sets but make sure the code for handling it is 100% bulletproof or your table will be like Humpty Dumpty.

Although adjacency lists are inefficient, there are methods of using them efficiently. I wrote a class for easy handling of Adjacency lists. You can download it from here:

http://www.pdvictor.com/en/?sv=&category=just%20code&title=adjacency%20model

+1 for "Nested sets...table structure is very delicate If it breaks it is very challenging to fix the table structure and it generally requires manual intervention. " — Marco Demaio, Dec 16 '12 at 16:46

score 0 · Answer 3 · answered Aug 23 '11 at 15:47

0

If you are recursively querying the DB multiple times (which apparently is the case), you will have performance issues.

What you can do is query all the data at once and then let PHP handle/organize the items recursively. You should be fine with that.

answered Aug 23 '11 at 15:47

Adriano Carneiro

57,693
12
90
123

Mysql adjacency list model alternative?

3 Answers3