I have a procedure that collects data from one table and inserts it into another table with the same columns, but after correlating with a few other tables. Since the existing table (from which data will be transferred) has more than a million records, the insertion into the other table (into which the data is being transferred) takes a very long time.
use acrs;
drop procedure getUA;
delimiter ;;
CREATE PROCEDURE getUA()
begin
DECLARE min INTEGER;
DECLARE max INTEGER;
select min(id) into min from user_info;
set max = min + 10000;
-- select max(id) into max from user_info;
CREATE TABLE IF NOT EXISTS user_activity_load(INDEX netactivity (id)) select * from user_info where id between min and max;
insert into user_activity_load (id, src_ip, dst_ip, src_port, dst_port, data, Created, smalicious, sgeoLoc, seq, app_id, tcp_flag, dgeoLoc, dmalicious)
select id, src_ip, dst_ip, src_port, dst_port, data, Created, smalicious, sgeoLoc, seq, app_id, tcp_flag, dgeoLoc, dmalicious from user_info where id between min and max;
DELETE from user_info where id >= min and id <=max;
WHILE (min <= max) DO
BEGIN
DECLARE src INTEGER unsigned;
DECLARE dst INTEGER unsigned;
DECLARE s_id INTEGER;
DECLARE d_id INTEGER;
select src_ip, dst_ip into src, dst from user_activity_load where id = min;
select id into s_id from ip2location_db3 where (src between ip_from and ip_to);
select id into d_id from ip2location_db3 where (dst between ip_from and ip_to);
update user_activity_load set sgeoLoc = s_id, dgeoLoc = d_id where id = min;
END;
set min = min + 1;
END WHILE;
end ;;
delimiter ;
Here user_info
is the table where the data currently is(before transfer), and user_activity_table
is where the data has to go. But a few columns in user_info
is empty, i.e sgeoLoc
and dgeoLoc
. These data are fetched from a separate table called ip2location
before being inserted into user_activity_load
. Currently the max
variable is set to 10000
because if set to max(id)
, then it will process a million logs and takes a lot of time. 10000
rows are currently taking 20 minutes to process.
Can somebody help me improve this so that it gets faster and processing a million logs does not take a day or so?