Stored procedure using cursor in mySql

Question

I wrote a stored procedure using cursor in mysql but that procedure is taking 10 second to fetch the result while that result set have only 450 records so, I want to know that why that proedure is taking that much time to fetch tha record.

procedure as below:

DELIMITER //
DROP PROCEDURE IF EXISTS curdemo123//

CREATE PROCEDURE curdemo123(IN Branchcode int,IN vYear int,IN vMonth int)
   BEGIN
      DECLARE  EndOfData,tempamount INT DEFAULT 0;
      DECLARE tempagent_code,tempplantype,tempsaledate CHAR(12);
      DECLARE tempspot_rate DOUBLE;
      DECLARE var1,totalrow INT DEFAULT 1;
      DECLARE cur1 CURSOR FOR 
              select SQL_CALC_FOUND_ROWS ad.agentCode
                   , ad.planType
                   , ad.amount
                   , ad.date 
                from adplan_detailstbl ad 
               where ad.branchCode=Branchcode 
                 and (    ad.date between '2009-12-1' 
                                      and '2009-12-31')
               order by ad.NUM_ID asc;
      DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET EndOfData = 1;
      DROP TEMPORARY TABLE IF EXISTS temptable;

      CREATE TEMPORARY TABLE temptable (
             agent_code varchar(15)
           , plan_type char(12)
           , sale double
           , spot_rate double default '0.0', dATE DATE
      );

      OPEN cur1;
      SET totalrow=FOUND_ROWS();
      while var1 <= totalrow DO
         fetch cur1 into tempagent_code
                        ,tempplantype
                        ,tempamount
                        ,tempsaledate;

         IF( (    tempplantype='Unit Plan' 
               OR tempplantype='MIP'
             ) OR tempplantype='STUP') THEN

            select spotRate into tempspot_rate 
              from spot_amount 
              where ((    monthCode=vMonth 
                      and year=vYear) 
                    and (     (     agentCode=tempagent_code 
                                and branchCode=Branchcode) 
                          and (planType=tempplantype)
              ));

            INSERT INTO temptable VALUES(
                tempagent_code
               ,tempplantype
               ,tempamount
               ,tempspot_rate
               ,tempsaledate)
           ;
      else
         INSERT INTO temptable(
              agent_code
             ,plan_type
             ,sale,dATE) 
         VALUES( tempagent_code,tempplantype,tempamount,tempsaledate);
      END IF;

      SET var1=var1+1;
END WHILE;
CLOSE cur1;
select * from temptable;
DROP TABLE  temptable;
END //
DELIMITER ;

Answer 1

please run the following and paste ALL the results into http://pastie.org/ you should replace the ?? in the select statements with proper test values before executing

 show table status like 'adplan_detailstbl'\G
 show create table adplan_detailstbl \G
 show indexes from adplan_detailstbl;

 explain select 
  ad.* 
 from 
  adplan_detailstbl ad 
 where 
  ad.branchCode = ?? and ad.date between '2009-12-01' and '2009-12-31'
 order by 
  ad.NUM_ID;

 show table status like 'spot_amount'\G
 show create table spot_amount \G
 show indexes from spot_amount;

 explain select * from 
  spot_amount
 where 
  monthCode=?? and year=?? and agentCode=?? and branchCode=?? and planType=??;