List:General Discussion« Previous MessageNext Message »
From:Jay J Date:January 7 2000 7:53pm
Subject:Re: "recursive" select?
View as plain text  
----- Original Message -----
From: "Jonathan Stimmel" <stimmj@stripped>
To: <mysql@stripped>
Sent: Friday, January 07, 2000 12:12 PM
Subject: "recursive" select?


> I imagine this falls under a FAQ, but I have been unsuccessful at finding
> a concrete answer to my situation. I also suspect that this is not
> possible with MySQL (currently using 3.22.27) without compiling and
> linking a new function into the server, but I'm still very new to SQL,
> so...
>
> I have a hierarchy/tree stored in a table, something like this:
>
[snip]
>
> +----------------+----------------------+---------------------------+
> | CategoryNumber | ParentCategoryNumber | CategoryName              |
> +----------------+----------------------+---------------------------+
> |          15101 |                    0 | Top Category              |
> |          15171 |                15101 | Middle Category           |
> |          15181 |                15171 | Current Category          |
> +----------------+----------------------+---------------------------+
> Jonathan Stimmel

Hi Jonathan,

You can find a recent thread in the archives on this topic:
http://www.progressive-comp.com/Lists/?l=mysql&w=2&r=1&s=masochist&q=b

During that thread, someone also mentioned it had come up before .. but I
never tracked down the earlier thread.

Sub-selects aren't implemented until >= 3.23 .. if that was one of your
questions.

I've done considerable fumbling on this topic, trying everything from [root,
parent, node] or 50 columns of zerofill int's to account for the depth of
the node while using CONCAT(a, '.', b), etc.. etc..

What I (and the originator of that thread) have concluded is that the most
optimal way (without sub-selects) is to wrangle a char column of period
delimited zerofill'ed numbers.

I've also chosen to keep a seperate column indicating the the root id, such
as this:

mysql> SELECT * FROM tree ORDER BY id_m;
+-------+---------------+------+
| id_t  | id_m          | cid  |
+-------+---------------+------+
| 00001 | 00001.        |    1 |
| 00001 | 00001.00001.  |    2 |
...
| 00001 | 00001.00002.  |    9 |
| 00002 | 00002.        |    5 |

The limitation is that it can only go about 42.5 nodes deep ( indexed
char(255)/6 ), but still 99,999 "down"

In the end, I determined this was the only possible way to do it - although
I tried very hard to find otherwise.

But, it does accomplish everything in a single select, and you can even play
with LIKE "00001._____." or count '.' characters to limit the depth (I spose
ya could just keep a depth column for simplicity).

I have yet to install any of the 3.23 releases, and would be interested to
see the performance difference on this method vs. sub-selects.

Hope this helps,

-Jay J

Thread
"recursive" select?Jonathan Stimmel7 Jan
  • Re: "recursive" select?Jay J7 Jan