mysql数据量很大时两种不同存储过程

自己写的存储过程 ：DROP PROCEDURE IF EXISTS `proc_getweathercities_test`$$
CREATE  PROCEDURE `epimap`.`proc_getweathercities_test`(param1 VARCHAR(21845))
   
    BEGIN
    SET @id = param1 ;
  SET @sel = 'SELECT   city,city_en,date_y,WEEK,cityid,temp1,temp2,temp3,temp4,temp5,temp6,weather1,weather2,weather3,weather4,weather5,weather6,wind1,wind2,wind3,wind4,wind5,wind6,fl1,fl2,fl3,fl4,fl5,fl6,ind,index_xs,index_uv,index_xc,index_tr,index_co,index_cl,index_ls,index_ag,create_time FROM(SELECT * FROM ep_weather_report WHERE city IN (' ;
  SET @se11 = ') or cityid in(' ;
  SET @sel2 = ')order by create_time desc) T group by city order by field(city,' ;
  SET @sel3 = ')' ;
  SET @sentence = CONCAT(@sel, @id, @se11, @id, @sel2, @id, @sel3);
  PREPARE stmt FROM @sentence ;
  EXECUTE stmt ;
  DEALLOCATE PREPARE stmt ;

    END;

组长写的：

DROP PROCEDURE IF EXISTS `weather_report_cities`$$

CREATE DEFINER=`epimap`@`%` PROCEDURE `weather_report_cities`(
IN p_citiesorurls VARCHAR(21845) ,
IN p_city_count INT,
OUT p_count INT
)
BEGIN
   DECLARE v_i INT  DEFAULT 1;
   DECLARE v_length INT;
   DECLARE v_city VARCHAR(20);

  -- set @v_city = v_city;
  SET v_length = p_city_count;
   TRUNCATE epimap.ep_weather_report_temp;
   TRUNCATE epimap.test_city;
-- SET SQL_SAFE_UPDATES=0; -- so slow on large talbe data using Innodb ,cause write data to log
-- delete from epimap.ep_weather_report_temp;
-- SET SQL_SAFE_UPDATES=1;
WHILE v_i<=v_length DO
   
   SELECT func_splitString(p_citiesorurls, ',', v_i) INTO v_city;   
    SET v_city = TRIM(v_city);
    INSERT INTO test_city VALUES(v_city);
    INSERT INTO epimap.ep_weather_report_temp
(city,city_en,date_y,WEEK,cityid,temp1,temp2,temp3,temp4,temp5,temp6,weather1,weather2,weather3,weather4,weather5,weather6,wind1,wind2,wind3,wind4,wind5,wind6,fl1,fl2,fl3,fl4,fl5,fl6,ind,index_xs,index_uv,index_xc,index_tr,index_co,index_cl,index_ls,index_ag,create_time
)
( SELECT * FROM
((SELECT city,city_en,date_y,WEEK,cityid,temp1,temp2,temp3,temp4,temp5,temp6,weather1,weather2,weather3,weather4,weather5,weather6,wind1,wind2,wind3,wind4,wind5,wind6,fl1,fl2,fl3,fl4,fl5,fl6,ind,index_xs,index_uv,index_xc,index_tr,index_co,index_cl,index_ls,index_ag,create_time FROM epimap.ep_weather_report
WHERE city = v_city    ORDER BY create_time DESC LIMIT 1)
UNION
(SELECT city,city_en,date_y,WEEK,cityid,temp1,temp2,temp3,temp4,temp5,temp6,weather1,weather2,weather3,weather4,weather5,weather6,wind1,wind2,wind3,wind4,wind5,wind6,fl1,fl2,fl3,fl4,fl5,fl6,ind,index_xs,index_uv,index_xc,index_tr,index_co,index_cl,index_ls,index_ag,create_time FROM epimap.ep_weather_report
 WHERE cityid = v_city    ORDER BY create_time DESC LIMIT 1)
) AS new_ep_weather_report_temp
) ;
  SET v_i=v_i+1;
END WHILE;
  SELECT COUNT(*) INTO p_count FROM  epimap.ep_weather_report_temp;
  SELECT *
 FROM epimap.ep_weather_report_temp;

END;

分析：自己写的在数据量很大，并且传入参数时，自己存储过程的速度是组长的好几倍，我是一次把数据传入，组长是循环查询，分析一下原因吧。。。。。。。。