title: MySQL: Stored Procedure Slow
body: <p>I have database table where I want to get all children (nth level) of specified parent. For that, I have used <a href="https://stackoverflow.com/a/13817113/1950456">answer from here</a> which is working fine.</p>
  
  <p>Here is code:</p>
  
  <pre><code>DELIMITER $$
  CREATE PROCEDURE getParents(in_id INT)
    BEGIN
      DROP TEMPORARY TABLE IF EXISTS results;
      DROP TEMPORARY TABLE IF EXISTS temp2;
      DROP TEMPORARY TABLE IF EXISTS temp1;
  
      CREATE TEMPORARY TABLE temp1 AS
        SELECT DISTINCT * FROM agents WHERE upline_id = in_id;
  
      CREATE TEMPORARY TABLE results AS
        SELECT * FROM temp1;
  
      WHILE (SELECT count(*) FROM temp1) DO
        CREATE TEMPORARY TABLE temp2 AS
          SELECT DISTINCT *
          FROM agents
          WHERE upline_id IN (SELECT id FROM temp1);
  
        INSERT INTO results SELECT * FROM temp2;
        DROP TEMPORARY TABLE IF EXISTS temp1;
  
        CREATE TEMPORARY TABLE temp1 AS
          SELECT * FROM temp2;
        DROP TEMPORARY TABLE IF EXISTS temp2;
  
      END WHILE;   
  
      SELECT * FROM results;
  
    END $$
  DELIMITER ;
  </code></pre>
  
  <p>Here is how I use it:</p>
  
  <p>call getParents(2060);</p>
  
  <p>Everything is fine but query runs slowly. Table also does not contain more than 10 thousand records.</p>
  
  <p><strong>Is there a way to optimize above stored procedure so that it runs a bit faster ?</strong></p>
  
  <p>I am new to MySQL so I cannot figure out how to optimize this stored procedure. Thanks for the help</p>