stored proc slow while ...

[BubikolRamios]

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 ?

[BubikolRamios]

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 !!!

[Old Pedant]

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

[BubikolRamios]

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 ...

[BubikolRamios]

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 ....

You see how time needed to process 100 records is increasing.