I just spent all morning working on a stored procedure that recursively selects categories from a table that has a parent/child relationship. I knew I could do this in PHP/CodeIgniter, but I thought I would gain some sort of performance advantage by going with the stored procedure. It turned out that the stored procedure was adding what seemed like a half second to the operation. In cases where the stored procedure was called multiple times, it was about 2+ seconds.
Then I just did it in PHP, and it's so fast I can't even tell there is any lag at all.
So, now I'm wondering why it's so slow. Is it slow because the creation of the temporary table makes it slow? Check it out:
Code:
DELIMITER $$
DROP PROCEDURE IF EXISTS get_ancestry $$
CREATE PROCEDURE get_ancestry( IN selected_cat_id int )
BEGIN
DECLARE parent_id int;
DECLARE child_id int;
SET child_id = selected_cat_id;
SET parent_id = 0;
-- Create temp table
CREATE TEMPORARY TABLE IF NOT EXISTS temp_table AS
( SELECT * FROM categories WHERE 1 = 0 );
TRUNCATE TABLE temp_table;
-- Add the selected category to the results
INSERT INTO temp_table
SELECT * FROM categories
WHERE id = selected_cat_id;
-- Get the parent of selected_cat_id
SELECT parent INTO parent_id
FROM temp_table WHERE id = selected_cat_id;
WHILE parent_id != 0 DO
-- Add this category to the temp table
INSERT INTO temp_table
SELECT * FROM categories
WHERE id = parent_id;
SET child_id = parent_id;
SET parent_id = 0;
-- This will be the next ancestor in the loop
SELECT parent into parent_id
FROM categories WHERE id = child_id;
END WHILE;
-- Return the complete ancestry
SELECT * FROM temp_table;
END $$
DELIMITER ;
