stored proc slow while ...

CREATE TABLE tree (CHILD_ID INTEGER NOT NULL,PARENT_ID INTEGER,TOP_PRIORITY_ORDER INTEGER DEFAULT NULL, INDEX (CHILD_ID,PARENT_ID)) ENGINE=MYISAM;
CREATE INDEX myIndex ON tree (parent_id);



CREATE TABLE tmp_NestedSetModel
(NestedSetModel_top INTEGER NOT NULL,
CHILD_ID INTEGER NOT NULL,
lft INTEGER,
rgt INTEGER,
PARENT_ID INTEGER,
ORDER_BY INTEGER,
INDEX (CHILD_ID,NestedSetModel_top)) ENGINE=MYISAM;


Slow part of proc.

Basicaly takes one record from tree tab and puts one into
tmp_NestedSetModel table.

It was all ok until I putted like 130.000 records into tree table.
It takes like 1-2 or more sec per one while loop.





WHILE @counter <= (@max_counter) DO

SELECT count(*) into v1
FROM tmp_NestedSetModel AS S1, tree AS T1
WHERE S1.CHILD_ID = T1.PARENT_ID
AND S1.NestedSetModel_top = @current_top;

/*call debug_insert('t',@current_top);*/



IF (v1 > 0) THEN

INSERT INTO tmp_NestedSetModel
SELECT (@current_top + 1),
MIN(T1.CHILD_ID),
@counter,
NULL,
NULL,
t1.top_priority_order
FROM tmp_NestedSetModel AS S1, tree AS T1
WHERE S1.CHILD_ID = T1.PARENT_ID
AND S1.NestedSetModel_top = @current_top;




DELETE FROM tree WHERE CHILD_ID = (SELECT CHILD_ID FROM tmp_NestedSetModel WHERE NestedSetModel_top = @current_top + 1);


SET @counter = @counter + 1;
SET @current_top = @current_top + 1;

ELSE




UPDATE tmp_NestedSetModel
SET rgt = @counter, NestedSetModel_top = -NestedSetModel_top
WHERE NestedSetModel_top = @current_top;



SET @counter = @counter + 1;
SET @current_top = @current_top - 1;
END IF;
END WHILE;


Any thought ?

Even vorse. After various indexs trial/error/esplain/sucess, I noticed that this processes like 100 records in a couple of seconds, then it slows down exponentialy. So 1000 records comes up like in 2 -3 hours !!!

Maybe if you told us what the purpose of this is?

converting adjacent to nested set hirearchical model
like here:
http://data.bangtech.com/sql/nested_set_treeview.htm

doh I doubt that info will make any difference.I fill like mysql is eating up memory predefined/not defined/default in various variables in my.cnf, with repeated delete, insert,update ...

so I added this into upper while, trying to do something on every 100 records
processed. No effect.

IF (MOD(@counter, 100) = 0) then
RESET QUERY CACHE;
FLUSH TABLES tree,tmp_NestedSetModel;
// adds some info into debug table
call debug_insert('convert_adjacent_to_nested','RESET QUERY CACHE');
END IF;


this is the result of upper call debug ....
http://www.shrani.si/t/a/C2/j3vA7Y/tmp.jpg (http://www.shrani.si/?a/C2/j3vA7Y/tmp.jpg)
You see how time needed to process 100 records is increasing.