phpMyAdmin hangs on proc with multiple SELECTS

Question

When I CALL this proc in MySQL, it just shows LOADING... and even freezes my server:

delimiter $$
CREATE PROCEDURE MyProc(IN index1 INT, IN count1 INT, IN index2 INT, IN count2 INT)
BEGIN
    SELECT id FROM view1 WHERE column1 IS NULL OR column1 = 1 ORDER BY dateColumn DESC LIMIT index1, count1;
    SELECT id FROM view1 WHERE column2 = 0 ORDER BY column3 DESC LIMIT index2, count2;
END$$
delimiter ;

If I only have one of either SELECTs, it works.

Anyone have any idea what I'm missing/doing wrong?

Many thanks in advance!

Details

I'm creating a proc for PHP so that I don't have to make so many connections to the database. Each SELECT needs to be its' own result set with column information for ease in PHP.

I need the slightly extra speed because my site is heavily dependent upon dynamic pagination.

Answer 1

What you are doing is quite sensible. This looks like a defect in phpmyadmin that may have been recently fixed, where stored procedures returning multiple result sets were not handled properly.

http://sourceforge.net/tracker/?func=detail&atid=377408&aid=3474853&group_id=23067

Answer 2

Try this:

DELIMITER $$
CREATE PROCEDURE MyProc(IN index1 INT, IN count1 INT, IN index2 INT, IN count2 INT)
BEGIN
    SET @s = CONCAT('SELECT id FROM view1 WHERE column1 IS NULL OR column1 = 1 ORDER BY dateColumn DESC LIMIT ', index1, ', ', count1);
    SET @s1 = CONCAT('SELECT id FROM view1 WHERE column2 = 0 ORDER BY column3 DESC LIMIT ', index2, ', ', count2);
    PREPARE stmt FROM @s;
    PREPARE stmt1 FROM @s1;
    EXECUTE stmt;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt;
    DEALLOCATE PREPARE stmt1;
END$$
DELIMITER ;