MySQL Query to Find the Root Element

NOTE: This is actually a database of a forum.

I am stuck in an Interesting problem. I created a database with the following fields

ID (primary), 
Data, 
ParentID

ParentID is the ID of some other row in the same table. However, this parent child relationship can extend upto few levels. The topmost elements has 0 as parentID.

The database is quite large (500 MB), and querying everytime for the root element for a row will be too time consuming. What I want to do here is, add another column rootID开发者_C百科. So if a particular data is 3 levels deep, it should have the ID of root (0 level) element in this column.

Can anyone help me out with the query syntax here?

I'll assume that root elements have ID = RootID. Since adding the root IDs is a one-off thing, I'd just start out with

UPDATE `table` SET RootID = ID

(after creating the new column, of course) and then run something like

UPDATE `table` t1 JOIN `table` t2 ON t1.RootID = t2.ID
   SET t1.RootID = t2.ParentID
 WHERE t2.ParentID <> 0

repeatedly until you see zero rows updated. (And later when you add a new row, you just set RootID to that of the parent, if it has one, or to itself -- you don't need to ascend to the root).

Use a stored function.

CREATE FUNCTION FindRootID(in_ID INT)
RETURNS int(11)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

DECLARE LastID INT DEFAULT in_ID;
DECLARE ParentID INT DEFAULT -1;

WHILE ParentID != 0 DO
    SET ParentID = (SELECT Parent FROM SomeTable WHERE ID = LastID);
    IF ParentID != 0 THEN
        SET LastID = ParentID;
    END IF;
END WHILE;

RETURN LastID;

END

Then:

UPDATE SomeTable SET RootID = FindRootID(ID);