...................
CREATE  TABLE tmp1 (
    id int NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
  )
  ENGINE = MEMORY
  CHARACTER SET utf8
  COLLATE utf8_general_ci SELECT
    tree.id AS n
  FROM tree
  WHERE tree.parent_id = @sid
  ORDER BY tree.`order`
  ;
  SELECT @d;  гдето ранее вычисляется, результат хранимки

/*
update LOW_PRIORITY
   tree
set
  `order`  =(select id from tmp1  WHERE tmp1.n=tree.id)
  WHERE id IN (SELECT n FROM tmp1)
;
*/
  

  CALL tmp();

PROCEDURE tmp()
BEGIN
update LOW_PRIORITY
   tree
set
  `order`  =(select id from tmp1  WHERE tmp1.n=tree.id)
  WHERE id IN (SELECT n FROM tmp1)
;
END

....................
 SELECT
    @d AS id;


  SELECT *FROM tmp1 t; ----- добавлено для теста,  таблица tmp1 создается правильно

 
  UPDATE LOW_PRIORITY android.tree
  SET `order` = (SELECT
      id
    FROM tmp1
    WHERE tmp1.n = android.tree.id)
  WHERE id IN (SELECT
      n
    FROM tmp1);
.............

.....................
DELETE LOW_PRIORITY QUICK
  FROM tmp1
    LIMIT 100;

INSERT LOW_PRIORITY IGNORE
  INTO tmp1(n)
  SELECT
    tree.id AS n
  FROM tree
  WHERE tree.parent_id = @sid
  ORDER BY tree.`order`;


  UPDATE LOW_PRIORITY android.tree
  SET `order` = (SELECT
      id
    FROM tmp1
    WHERE tmp1.n = android.tree.id)
  WHERE id IN (SELECT
      n
    FROM tmp1);