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 ;
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
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?