I need to load data into MYSQL table using LOAD data. At boot time, I need to set the data flag column for the corresponding value based on certain conditions. Data insertion can come from different scenarios, and I thought about using a trigger BEFORE INSERT to centralize the process. Now the problem is that the INSERT process is time consuming. As a sample, when I used the SOURCE command from the command line with a file of 500,000 records to place test data, a block of 550 records takes about 19 seconds to enter, starting from an empty table. When I used LOAD data with approximately 2,300 records (these are ideal inserts that can occur on a regular basis, unless the previous inserts have completed and the data that needs to be inserted is accumulated), it took about 90 seconds to complete.
I'd like to know:
If I can in any case improve the performance of this trigger, as it is, or are the triggers generally slow?
If I change the same logic to an external trigger so that regular SQL improves processing performance during processing (Trigger vs regular SQL). Sorry, for some reason I could not test this scenario. Also, since I have many scenarios from which data may arise, I would like to avoid this.
My startup logic
CREATE TRIGGER `mydb`.`flag_data` BEFORE INSERT ON `mydb`.`mytable` FOR EACH ROW BEGIN DECLARE threshold_val FLOAT; DECLARE time_upper_limit_1 FLOAT; DECLARE time_upper_limit_2 FLOAT; SET threshold_val = 400; SET time_upper_limit_1 = 4000; SET time_upper_limit_2 = 8000; SET new.data_flag=(SELECT CASE COUNT(*) WHEN 0 THEN 2 ELSE (SELECT CASE WHEN new.rf >@threshold_val THEN 5 WHEN new.rf < 0 THEN 5 ELSE (SELECT CASE WHEN MINUTE( new.rec_time) Mod 15 <> 0 THEN 1 ELSE new.data_flag END) END) END FROM vw_active_stn_list WHERE stn_id=new.stn_id); IF (new.data_flag = 0) THEN IF (new.rmode = 'H') THEN SET new.data_flag=(SELECT CASE WHEN COUNT(*)>0 THEN 2 ELSE 0 END FROM vw_mf_list WHERE stn_id=new.stn_id); ELSEIF (new.rmode = 'F') THEN SET new.data_flag=(SELECT CASE WHEN COUNT(*) > 0 THEN 4 ELSE (SELECT CASE WHEN ISNULL(TIMESTAMPDIFF(MINUTE,new.rec_time,Now()))=1 THEN 1 WHEN TIMESTAMPDIFF(MINUTE,new.rec_time,Now()) NOT BETWEEN 0 AND @time_upper_limit_1 THEN 1 ELSE 0 END ) END from stn_mf WHERE ((new.rec_time BETWEEN mf_start_time AND mf_end_time) OR (mf_start_time <= new.rec_time AND mf_end_time IS NULL)) AND stn_id=new.stn_id AND (stn_type='X' OR stn_type='Y')); ELSEIF (new.rmode = 'S' OR new.rmode = 'M') THEN SET new.data_flag=(SELECT CASE WHEN COUNT(*) > 0 THEN 4 ELSE (SELECT CASE WHEN ISNULL(TIMESTAMPDIFF(MINUTE,new.rec_time,Now()))=1 THEN 1 WHEN TIMESTAMPDIFF(MINUTE,new.rec_time,Now()) NOT BETWEEN 0 AND @time_upper_limit_2 THEN 1 ELSE 0 END ) END from stn_mf WHERE ((new.rec_time BETWEEN mf_start_time AND mf_end_time) OR (mf_start_time <= new.rec_time AND mf_end_time IS NULL)) AND stn_id=new.stn_id AND (stn_type='X' OR stn_type='Y')); END IF; END IF; END
source share