Mysql Bulk更新机能改造(当…案例时)

我有这样的查询,应该在每个MySQL操纵后执行.此查询此刻正在减慢页面加载速率,由于数据增进了,我做了全部工作,如正确的索引,但查询如故相对较慢.

有没有其他要领来执行这些搜查？

$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
";

假如是这样,相同于Endre上面的办理方案,但只是做一个平凡的毗连而不是对子查询的毗连.

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

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!