How to properly loop in a stored function on MySQL?

Question

I am having some difficulty getting a pretty simple stored procedure right. Consider the following article table snippet:

id    replaced_by     baseID
 1              2          0
 2              3          0
 3              0          0

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.

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.

To get the baseID, I have created the following stored procedure:

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 ;

It seems simple enough, until I actually call the function using:

SELECT getBaseID(3);

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

I have even rewritten the same function using REPEAT .. UNTIL and with WHILE .. DO, with the same end result.

Can anyone explain why my CPU goes up 100% when it enters the loop?

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.

Thanks in advance for any assistance and/or pointers.

---

Solved SQL:

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 ;

Tennietenniel

Answer 1

From mysql :

If the query returns no rows, a warning with error code 1329 occurs (No data), and the variable values remain unchanged

So you have an infinite loop when no records found with a given x (y remains unchanged) Try SET y = (SELECT id ....) instead or add SET y = null before your select statement (it should be the first statement in the loop)

Sacrum

Omg, thank you so much. I did read exactly that page on the INTO bit, but I didn't understand what they meant with vars not changing. – Tennietenniel

Answer 2

It feels like you may be missing an index on the replaced_by column. If there no index on replaced_by, you are looking at a full table scan with every iteration.

ALTER TABLE article ADD INDEX (replaced_by);

You should also make sure the row exists before retrieving

DELIMITER $$

CREATE FUNCTION getBaseID(articleID INT) RETURNS INT
BEGIN
    DECLARE x INT;
    DECLARE y INT;
    SET x = articleID;
    sloop:LOOP
        SELECT COUNT(1) INTO y FROM article WHERE replaced_by = x;
        IF y > 0 THEN
            SELECT id INTO y FROM article WHERE replaced_by = x;
            SET x = y;
        ELSE
            LEAVE sloop;
        END IF;  
    END LOOP;
    RETURN x;
END $$

DELIMITER ;

Twice as many SQL calls but better safe than sorry.

Give it a Try !!!

Ten

Yes, indexes are available. But searching for non-existing rows is what I am testing for. It indicates when I have found the baseID! – Tennietenniel