info testing mysql_关于mysql存储过程执行时间长，求优化思路？

现有一张包含经(lon)纬(lat)度的A (c_pc_stake_info)表,在另一张B表( b_pc_compaction_info)中也包含有经(lon)纬(lat)度字段，需要返回的数据是A表的经纬度、通过A表每两条数据的经纬度和B表的经纬度相比较，得到B表数据在A表这两条数据经纬度之间的个数

CREATE DEFINER=root@%PROCEDUREcompaction(

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

第一次用csdn，问题有些格式不是太对，望大佬们谅解