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 )
BEGIN
  INSERT IGNORE INTO table1 (v1, v2)
  SELECT v1, v2 FROM table2 WHERE v3=in_value
END

CREATE PROCEDURE p_all ()
BEGIN
    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;

    OPEN my_cursor;
    my_loop: LOOP
       FETCH my_cursor INTO my_id;
       IF finished THEN
         LEAVE my_loop;
       END IF;
       CALL p_each_value( my_id );
       SET finished = 0;
    END LOOP my_loop;
    CLOSE my_cursor;
END

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.

http://www.bluebox.net/news/2009/07/mysql_encoding

I have not used prepared statements before, this looks like a great feature for repetitive queries.

MySQL :: Prepared Statements

So in pseudo code:

prepare sql
do
     set parameter(s)
     execute
     actions….
done
release

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.