2
\$\begingroup\$

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?

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.