Mysql Bulk update performance improvements for (when.. case)

Question

I had a query like this, that should be executed after each MySQL actions. This query is now slowing down page loading because the data increased, I did everything like proper indexing, but the query is still slow relatively.

Is there any other way to perform those 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
";

Cutpurse

after each MySQL action??? is there a real need to do that? Why don't you use a trigger? – Greenfield

@simon: its really confusing using long trigger and when it has too many columns, however, if you are sure that trigger solves this issue I will use it. – Cutpurse

I do not know your business logic: if the trigger will replace your need of bulk operations and will do only for one row - it will solve a problem. As far as your operations are not dependent on other rows from this table I think you can just use trigger for each row (which is changed only) and not for all of them. – Greenfield

Simon is correct. This type of update is perfectly suited for a one-time data fix, but not for ongoing data corrections. If the code that inserts or updates wh_profg.status can not be modified to use the new status codes, then INSERT and UPDATE triggers will provide the best performance. They give you the ability to limit your data correction to the one row that is affected instead of updating every row in the wh_profg table. Having said that, I think you would need INSERT and UPDATE triggers on both wh_profg and wh_profulldetail tables. – Darnley

... couldn't really provide a solution without knowing the relationship between the two tables. – Darnley

@gwc: these tables are related by one field (batchno), wh_profg is the main table, and wh_profulldetail is view of that table and other table wh_profg_usage (its complicated query). – Cutpurse

@Jason OOO - I get that they are related by batchno. The question is: is it a one-to-one relationship? I.e. is there ever only one row in wh_profg_usage for a given batchno or can there be multiple rows with the same batchno. If you want a complete, accurate answer, it would help if you would post the table structures and the full query, and possibly even some sample data so the answer can be tested. – Darnley

Pretty sure your performance issues are due to updating every row in wh_profg every time you run your query. That's why you didn't notice it until after the table had grown to a certain size. – Darnley

@gwc: no its not one to one, can there be multiple rows with the same batchno yes. – Cutpurse

Answer 1

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

If so, similar solution to that by Endre above, but just doing a plain join rather than a join against 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

Demonism

Answer 2

From the clarifications, I think you are saying that wh_profulldetail is a view that joins wh_profg and wh_profg_usage. Any inserts or updates to wh_profg_usage could affect wh_profg.status. Consequently, you will need INSERT and UPDATE triggers on both wh_profg and wh_profg_usage.

Once the triggers have been created, eliminate your original UPDATE query. The update will be performed by the triggers when needed. In addition, the update will only be performed on the one wh_profg row that is affected. This should eliminate your performance issues because your current update query is updating EVERY row in wh_profg.

The triggers would look something like:

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 wh_profulldetail view, it's pretty tough to completely nail this.

Darnley

oh this is very detailed triggers, I think I will finally go in this direction. – Cutpurse