我有这样的查询,应该在每个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
";
最佳答案
wh_profg和wh_profulldetail之间是否存在一对一的关系？
如果是这样,类似于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