Mysql Bulk performance improvement for (when .. case)

Question

I had a query that should be executed after every MySQL action. This query now slows down the page loading as the data grows, I did everything like proper indexing, but the query is still relatively slow.

Is there any other way to perform these checks?

$query = " UPDATE {$tprefix}wh_profg SET status = CASE WHEN batchno in ( select batchno from {$tprefix}wh_profulldetail where remainingdays <= 0 ) THEN 'expired' WHEN QC = 'rejected' and QA != 'rejected' and status != 'expired' THEN 'QC-rejected' WHEN QA = 'rejected' and QC != 'rejected' and status != 'expired' THEN 'QA-rejected' WHEN QA = 'rejected' and QC = 'rejected' and status != 'expired' THEN 'QA&QC-rejected' WHEN ( batchno in ( select batchno from {$tprefix}wh_profulldetail where available <= 0 ) ) and status != 'expired' and status NOT LIKE '%rejected' THEN 'empty' WHEN QC ='quarantine' and status != 'empty' and status != 'expired' and batchno in ( select batchno from {$tprefix}wh_profulldetail where available > 0 and remainingdays > 0 ) THEN 'quarantine' WHEN QC ='approved' and QA = 'approved' and status != 'empty' and status != 'expired' and status NOT LIKE '%rejected' and batchno in ( select batchno from {$tprefix}wh_profulldetail where available > 0 and remainingdays > 0 ) THEN 'available' ELSE 'unknown' END "; 

Answer 1

Is there a 1 to 1 relationship between wh_profg and wh_profulldetail?

If so, then a similar solution for the Endre solution is higher, but a simple connection is just made, not a connection with a subquery.

 UPDATE {$tprefix}wh_profg a LEFT OUTER JOIN {$tprefix}wh_profulldetail b ON a.batchno = b.batchno SET a.status = CASE WHEN b.batchno IS NOT NULL AND b.remainingdays <= 0 THEN 'expired' WHEN a.QC = 'rejected' and a.QA != 'rejected' and a.status != 'expired' THEN 'QC-rejected' WHEN a.QA = 'rejected' and a.QC != 'rejected' and a.status != 'expired' THEN 'QA-rejected' WHEN a.QA = 'rejected' and a.QC = 'rejected' and a.status != 'expired' THEN 'QA&QC-rejected' WHEN b.batchno IS NOT NULL AND b.available <= 0 and a.status != 'expired' and a.status NOT LIKE '%rejected' THEN 'empty' WHEN a.QC ='quarantine' and a.status != 'empty' and a.status != 'expired' and b.batchno IS NOT NULL AND b.available > 0 and b.remainingdays > 0 THEN 'quarantine' WHEN a.QC ='approved' and a.QA = 'approved' and a.status != 'empty' a.and status != 'expired' a.and status NOT LIKE '%rejected' and b.batchno IS NOT NULL AND available > 0 and remainingdays > 0 THEN 'available' ELSE 'unknown' END 

Answer 2

From the clarifications, I think you are saying that wh_profulldetail is a view that combines wh_profg and wh_profg_usage. Any insertions or updates to wh_profg_usage may affect wh_profg.status. Therefore, you will need INSERT and UPDATE triggers for wh_profg and wh_profg_usage.

Once the triggers have been created, eliminate the original UPDATE query. Updates will be performed by triggers when necessary. In addition, the update will only be performed on the wh_profg line that is affected. This should fix your performance problems because your current update request updates EVERY line in wh_profg.

Triggers will look something like this:

 DELIMITER // CREATE TRIGGER insert_wh_profg_usage AFTER INSERT ON wh_profg_usage FOR EACH ROW UPDATE wh_profg a SET status = status WHERE batchno = NEW.batchno; // CREATE TRIGGER update_wh_profg_usage AFTER UPDATE ON wh_profg_usage FOR EACH ROW UPDATE wh_profg a SET status = status WHERE batchno = NEW.batchno; // CREATE TRIGGER update_wh_profg_usage AFTER DELETE ON wh_profg_usage FOR EACH ROW UPDATE wh_profg a SET status = status WHERE batchno = NEW.batchno; // CREATE TRIGGER insert_wh_profg BEFORE INSERT ON wh_profg FOR EACH ROW BEGIN SELECT 1 ,remainingdays ,available INTO @detailfound ,@remainingdays ,@available FROM wh_profulldetail WHERE batchno = NEW.batchno; IF @remainingdays <= 0 THEN SET NEW.status = 'expired'; ELSEIF NEW.qc = 'rejected' AND NEW.qa != 'rejected' AND new.status != 'expired' THEN SET NEW.status = 'QC-rejected'; ELSEIF NEW.qa = 'rejected' AND NEW.qc != 'rejected' AND new.status != 'expired' THEN SET NEW.status = 'QA-rejected'; ELSEIF NEW.qa = 'rejected' AND NEW.qc = 'rejected' AND new.status != 'expired' THEN SET NEW.status = 'QA&QC-rejected'; ELSEIF NEW.status != 'expired' AND NEW.status NOT LIKE '%rejected' AND @detailfound = 1 AND @remainingdays <= 0 THEN SET NEW.status = 'empty'; ELSEIF NEW.qc ='quarantine' AND NEW.status != 'empty' AND NEW.status != 'expired' AND @detailfound = 1 AND @available > 0 AND @remainingdays > 0 THEN SET NEW.status = 'quarantine'; ELSEIF NEW.qc = 'approved' AND NEW.qa = 'approved' AND NEW.status != 'empty' AND NEW.status != 'expired' AND NEW.status NOT LIKE '%rejected' AND @detailfound = 1 AND @available > 0 AND @remainingdays > 0 THEN SET NEW.status = 'available'; ELSE SET NEW.status = 'unknown'; END IF; END; // CREATE TRIGGER update_wh_profg BEFORE UPDATE ON wh_profg FOR EACH ROW BEGIN SELECT 1 ,remainingdays ,available INTO @detailfound ,@remainingdays ,@available FROM wh_profulldetail WHERE batchno = NEW.batchno; IF @remainingdays <= 0 THEN SET NEW.status = 'expired'; ELSEIF NEW.qc = 'rejected' AND NEW.qa != 'rejected' AND new.status != 'expired' THEN SET NEW.status = 'QC-rejected'; ELSEIF NEW.qa = 'rejected' AND NEW.qc != 'rejected' AND new.status != 'expired' THEN SET NEW.status = 'QA-rejected'; ELSEIF NEW.qa = 'rejected' AND NEW.qc = 'rejected' AND new.status != 'expired' THEN SET NEW.status = 'QA&QC-rejected'; ELSEIF NEW.status != 'expired' AND NEW.status NOT LIKE '%rejected' AND @detailfound = 1 AND @remainingdays <= 0 THEN SET NEW.status = 'empty'; ELSEIF NEW.qc ='quarantine' AND NEW.status != 'empty' AND NEW.status != 'expired' AND @detailfound = 1 AND @available > 0 AND @remainingdays > 0 THEN SET NEW.status = 'quarantine'; ELSEIF NEW.qc = 'approved' AND NEW.qa = 'approved' AND NEW.status != 'empty' AND NEW.status != 'expired' AND NEW.status NOT LIKE '%rejected' AND @detailfound = 1 AND @available > 0 AND @remainingdays > 0 THEN SET NEW.status = 'available'; ELSE SET NEW.status = 'unknown'; END IF; END; // delimiter ; 

Without table structures or sample data or a wh_profulldetail view, it's pretty hard to completely fake.