Working with MySQL 5.5.9, I ran into an issue where a cursor loop in 1 procedure was getting stopped by the INSERT … SELECT in a called procedure. The fix was not pretty but was effective: put a SET in the code to force the HANDLER back to FALSE(0) after the CALL that was setting it to TRUE(1).
CREATE PROCEDURE p_each_value( in_value INT )
INSERT IGNORE INTO table1 (v1, v2)
SELECT v1, v2 FROM table2 WHERE v3=in_value
CREATE PROCEDURE p_all ()
DECLARE my_id INT;
DECLARE finished INT(1) DEFAULT 0;
DECLARE my_cursor CURSOR FOR
SELECT v3 FROM table2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
FETCH my_cursor INTO my_id;
IF finished THEN
CALL p_each_value( my_id );
SET finished = 0;
END LOOP my_loop;
This looks like it is going to be an issue for me in the near future.
Looks like everything needs to go from latin1 to utf8.
I have not used prepared statements before, this looks like a great feature for repetitive queries.
MySQL :: Prepared Statements
So in pseudo code:
Did this on a fairly involved php page and quite honestly the data was inconclusive at best:
CASE Queries Run 1 Run 2 Run 3
Baseline 4013 41s 42s 42s
Prepare 5019 41s 40s 39s
More Optimize 4019 42s 40s 40s
I think it helped but it really went to show that the SQL part of the page was not making that much difference (this had one major table with about 42,000 rows)
This was done on a VM test server platform(linux) from the host os as the browser (Mac). Before Run 1 each time I restarted both Apache & MySQL to eliminate the possibility of caching effects.