decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5 i recently testing the move of our db from one server to another.
as part of this process i copied all the data to the new db and started testing it.
as a comparison i run a store procedure whose execution on the old DB took about 2.5 hours on the new db it took about 8.
now the amount of data is the same and the sp is the same the differences is in:

old db

mysql version 5.0.45
pc 
OS Name Microsoft Windows XP Professional
Version 5.1.2600 Service Pack 3 Build 2600
Processor   x86 Family 6 Model 15 Stepping 13 GenuineIntel ~2394 Mhz
Total Physical Memory   2,048.00 MB


new db

mysql version 5.5.25a
pc
OS Name Microsoft(R) Windows(R) Server 2003, Standard Edition
Version 5.2.3790 Service Pack 2 Build 3790
System Manufacturer Dell Inc.
System Model    PowerEdge 2900
System Type X86-based PC
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Total Physical Memory   4,090.64 MB


if any one has any idea what to check i will be happy to hear.

one of the SP that takes now alot of time is:

BEGIN
DECLARE tempSlot INT(10);
DECLARE tempPort INT(10);
DECLARE tempOnx INT(10);
DECLARE tempLagId INT(10);
DECLARE tempIp TEXT;

DROP TABLE IF EXISTS `temp_ports_table`;
CREATE TEMPORARY TABLE `temp_ports_table` (
  `ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `IP` TEXT,
  `LAG` INT(10) UNSIGNED ,
  `SLOT` INT(10) UNSIGNED ,
  `PORT` INT(10) UNSIGNED ,
  `ONX` INT(10) UNSIGNED , 
    PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO temp_ports_table (IP, LAG)
    SELECT DISTINCT IP, LAG_ID
    FROM  n_to_1_table        
    WHERE LAG_ID IS NOT NULL AND LAG_ID &gt; 0;
INSERT INTO temp_ports_table (IP, SLOT, PORT, ONX)
    SELECT DISTINCT IP, SLOT, PORT, ONX
    FROM  n_to_1_table        
    WHERE LAG_ID IS NULL OR LAG_ID &lt;= 0;
SET @numOfPortsInNto1Table:=(
    SELECT count(ID) 
    FROM temp_ports_table accessChassis 
    GROUP BY ID&gt;0);
IF @numOfPortsInNto1Table&lt;=0 THEN
  SET @numOfPortsInNto1Table:=0;
END IF;
 INSERT INTO debug_table(DATE, TIME, RESULT) SELECT CURDATE(), CURTIME(), 'N:1 Modifications Stored Procedure : Created Temporary Tables';

SET @tempNto1tableCounter:=1;
WHILE @tempNto1tableCounter&lt;=@numOfPortsInNto1Table DO

    SET @tempFlowInstanceId := 0;

    SELECT tempPortsTable.SLOT, tempPortsTable.PORT, tempPortsTable.ONX, tempPortsTable.LAG, tempPortsTable.IP 
        INTO tempSlot, tempPort, tempOnx, tempLagId, tempIp
            FROM temp_ports_table tempPortsTable 
            WHERE tempPortsTable.ID=@tempNto1tableCounter;

    IF (tempLagId IS NOT NULL AND tempLagId &gt; 0) THEN
        UPDATE n_to_1_table 
            SET INSTANCE_ID=@tempFlowInstanceId:=@tempFlowInstanceId+1 
            WHERE 
                IP=tempIp AND 
                LAG_ID=tempLagId;
    ELSE
        UPDATE n_to_1_table 
            SET INSTANCE_ID=@tempFlowInstanceId:=@tempFlowInstanceId+1 
            WHERE 
                IP=tempIp AND 
                SLOT=tempSlot AND 
                PORT=tempPort AND 
                ((tempOnx IS NULL AND ONX IS NULL) OR ONX=tempOnx) AND 
                (LAG_ID IS NULL OR LAG_ID &lt;= 0);
    END IF;
    SET @tempNto1tableCounter:=@tempNto1tableCounter+1;

END WHILE;
 INSERT INTO debug_table(DATE, TIME, RESULT) SELECT CURDATE(), CURTIME(), 'N:1 Modifications Stored Procedure : Modified All N:1 Flow INstance ID According To Interface';
 DROP TABLE IF EXISTS `temp_ports_table`;
 INSERT INTO debug_table(DATE, TIME, RESULT) SELECT CURDATE(), CURTIME(), 'N:1 Modifications Stored Procedure Finished';
END
If all your data is InnoDB, I have a surprise for you.

It is a little-know fact, but there are some occasions when an older version of MySQL/InnoDB can outrun/outgun a newer of MySQL/InnoDB. How can that be possible?

I wrote about this in DBA StackExchange before


Why mysql 5.5 slower than 5.1 (linux,using mysqlslap) (Nov 24, 2011)
Query runs a long time in some newer MySQL versions (Oct 05, 2011)
Multi cores and MySQL Performance (Sep 20, 2011)
How do I properly perform a MySQL bake-off? (Jun 19, 2011)


You will have to perform some due diligence in configuring MySQL to harness more threading and more CPUs.