title: How to properly loop in a stored function on MySQL?
body: <p>I am having some difficulty getting a pretty simple stored procedure right.
  Consider the following article table snippet:</p>
  
  <pre><code>id    replaced_by     baseID
   1              2          0
   2              3          0
   3              0          0
  </code></pre>
  
  <p>A simple hierarchical table, using copy-on-write. When an article is edited, the replaced_by field of the current article is set to the id of it's new copy.</p>
  
  <p>I've added a baseID field, which in the future should store the baseID of an article.
  In my example above, there is one article (eg id 3). It's baseID would be 1.</p>
  
  <p>To get the baseID, I have created the following stored procedure:</p>
  
  <pre><code>DELIMITER $$
  
  CREATE FUNCTION getBaseID(articleID INT) RETURNS INT
  BEGIN
      DECLARE x INT;
      DECLARE y INT;
      SET x = articleID;
      sloop:LOOP
          SELECT id INTO y FROM article WHERE replaced_by_articleID = x;
          IF y IS NOT NULL THEN
              SET x = y;
              ITERATE sloop;
          ELSE
              LEAVE sloop;
          END IF;  
      END LOOP;
      RETURN x;
  END $$
  
  DELIMITER ;
  </code></pre>
  
  <p>It seems simple enough, until I actually call the function using:</p>
  
  <pre><code>SELECT getBaseID(3);
  </code></pre>
  
  <p>I would expect, the function to return 1. I'm even willing to understand it can take a slice of a second.
  Instead, the machine's CPU goes up to 100% (mysqld).</p>
  
  <p>I have even rewritten the same function using <code>REPEAT .. UNTIL</code> and with <code>WHILE .. DO</code>, with the same end result.</p>
  
  <p>Can anyone explain why my CPU goes up 100% when it enters the loop?</p>
  
  <p>Side note: I am trying to simply win time. I have created the exact same function in PHP, which performs okay, but our guess is that MySQL can do it slightly faster. We need to sift through about 18 million records. Any bit of time I can save is going to be worth it.</p>
  
  <p>Thanks in advance for any assistance and/or pointers.</p>
  
  <hr>
  
  <p>Solved SQL:</p>
  
  <pre><code>DELIMITER $$
  
  CREATE FUNCTION getBaseID(articleID INT) RETURNS INT
  BEGIN
      DECLARE x INT;
      DECLARE y INT;
      SET x = articleID;
      sloop:LOOP
          SET y = NULL;
          SELECT id INTO y FROM article WHERE replaced_by_articleID = x;
          IF y IS NULL THEN
              LEAVE sloop;
          END IF;  
          SET x = y;
          ITERATE sloop;
      END LOOP;
      RETURN x;
  END $$
  
  DELIMITER ;
  </code></pre>