mysql存储过程执行时间长，求优化思路？ MySQL问题

现有一张包含经(lon)纬(lat)度的A （c_pc_stake_info）表,在另一张B表（ b_pc_compaction_info）中也包含有经(lon)纬(lat)度字段，需要返回的数据是A表的经纬度、通过A表每两条数据的经纬度和B表的经纬度相比较，得到B表数据在A表这两条数据经纬度之间的个数 CREATE DEFINER=root@% PROCEDURE compaction( out num INTEGER, out startLon DOUBLE, out startLat DOUBLE, out startStakeNum varchar(100), out endLon DOUBLE, out endLat DOUBLE, out endStakeNum varchar(100) ) BEGIN -- 定义变量 DECLARE s int DEFAULT 0; DECLARE stakeId INTEGER; DECLARE lons DOUBLE; DECLARE lats DOUBLE; DECLARE stakeNum VARCHAR(50); DECLARE compaction_lon DOUBLE; DECLARE compaction_lat DOUBLE; -- 临时表 -- 封装最后所得数据 create temporary table if not exists stake_info_vo ( number INTEGER, startLon DOUBLE, startLat DOUBLE, startStakeNum VARCHAR(50), endLon DOUBLE, endLat DOUBLE, endStakeNum VARCHAR(50) ); -- 经B表筛选过的数据插入此表 create temporary table if not exists lon_lat (

lon1 DOUBLE, lat1 DOUBLE ); BEGIN -- B表筛选后的数据 declare lonLat CURSOR for SELECT lon, lat from b_pc_compaction_info where 1=1 and SUBSTRING_INDEX(create_time," ",1) = SUBSTRING_INDEX((select max(create_time) from b_pc_compaction_info)," ",1) -- ; and LENGTH(SUBSTRING_INDEX(lon,".",-1)) = 8 and LENGTH(SUBSTRING_INDEX(lat,".",-1)) = 8; -- insert into lon_lat VALUES (compaction_lon,compaction_lat); DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1; open lonLat; fetch lonLat into compaction_lon,compaction_lat; set s = 0; while(s=0) do insert into lon_lat VALUES (compaction_lon,compaction_lat); fetch lonLat into compaction_lon,compaction_lat; end while; -- 关闭游标 close lonLat; END; BEGIN -- 定义游标，并将sql结果集赋值到游标中 DECLARE report CURSOR FOR -- 查询A表数据 select id,lon,lat,stake_num from c_pc_stake_info ORDER BY id; -- 声明当游标遍历完后将标志变量置成某个值 DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;

-- 打开游标 open report;

-- 将游标中的值赋值给变量，注意：变量名不要和返回的列名同名，变量顺序要和sql结果列的顺序一致
fetch report into stakeId,lons,lats,stakeNum;
        set s=0;

-- 当s不等于1，也就是未遍历完时，会一直循环
while (s<>1) do

    -- 通过遍历获取A表的第二条数据
  SELECT 
   lon,
   lat,
  stake_num
   into endLon,endLat,endStakeNum
FROM c_pc_stake_info
WHERE id = (SELECT MIN(id) FROM c_pc_stake_info WHERE id > stakeId);

        -- 获取B表数据在A表每两条数据经纬度之间的个数
                 SELECT count(1) into num
                 from lon_lat 
                 where 1=1
             and lon1 between lons and endLon
                 and lat1 between lats and endLat
            ;


        SET startLon  = lons;
 set startLat  = lats;      
 set startStakeNum  = stakeNum;
         -- set num = 1;
 INSERT INTO stake_info_vo VALUES (num,startLon,startLat,startStakeNum,endlon,endlat,endStakeNum);  

    -- 当s等于1时表明遍历以完成，退出循环
    fetch report into stakeId,lons,lats,stakeNum;
end while;

-- 关闭游标 close report; SELECT * from stake_info_vo; -- SELECT * from lon_lat; end; truncate TABLE stake_info_vo; truncate TABLE lon_lat; END