我有以下表格：
wi_individiual_g(ind_id, ind_name, ...)
wi_training(trn_id, trn_name, ...)
wi_group(grp_id, grp_name, ...)
在这里，一个群体可以由以下个人组成：
wi_indv_org(ind_id, grp_id, is_deleted)
培训可以提供给一个团体或个人。也就是说，训练可以由多个组和其他单独的个人组成（也可以来自不属于训练的其他组）。为此，我创建了两个表：
wi_trn_org(trn_id, grp_id, is_deleted)
wi_indv_training(ind_id,trn_id,is_deleted)
在wi_indv_training中，个人来自正在训练的组或来自如上所述的匿名组。在输入超过200K的数据后，我在wi_indv_training中添加了一列indv_source。作为
wi_indv_training(ind_id,trn_id,indv_source, is_deleted)
此列检查个人是来自组还是匿名组。如果个人来自组，则标记为group，否则标记为individual。这个概念适用于新输入的数据。但我需要更新之前的数据20万，以检查参与培训的个人是团体来源还是个人来源。为了解决这个问题，我编写了这段PHP代码，但执行起来需要超过15分钟。
$trn=$this->db->pdoQuery("SELECT DISTINCT(trn_id) AS trn_id FROM wi_indv_training WHERE is_deleted=0")->results();
foreach ($trn as $key => $value) {
$trn_id=$value['trn_id'];
$grp=$this->db->pdoQuery("UPDATE wi_indv_training SET indv_source='Group'
WHERE EXISTS
(
SELECT DISTINCT(wi_indv_org.ind_id) FROM wi_indv_org
INNER JOIN wi_trn_org ON wi_indv_org.grp_id=wi_trn_org.grp_id AND wi_trn_org.is_deleted=0
AND wi_trn_org.is_deleted=0
AND wi_trn_org.trn_id=$trn_id
)
AND ind_id IN (
SELECT DISTINCT(wi_indv_org.ind_id) FROM wi_indv_org
INNER JOIN wi_trn_org ON wi_indv_org.grp_id=wi_trn_org.grp_id AND wi_trn_org.is_deleted=0
AND wi_trn_org.is_deleted=0
AND wi_trn_org.trn_id=$trn_id
);UPDATE wi_indv_training SET indv_source='Individual' WHERE trn_id=$trn_id AND ind_id NOT IN (
SELECT DISTINCT(wi_indv_org.ind_id) FROM wi_indv_org
INNER JOIN wi_trn_org ON wi_indv_org.grp_id=wi_trn_org.grp_id AND wi_trn_org.is_deleted=0
AND wi_trn_org.is_deleted=0
AND wi_trn_org.trn_id=$trn_id
)");
}
SQL中的要求是：；
Step 1) To select a training.
Step 2) Search for groups that are involved in training.
Step 3) Search for individuals that are in groups provided by above step
Step 4) Update the table wi_indv_training records as indv_source='Group' if records found for that training
Step 5) Update all remaining records as indv_source='Individual' for provided training.
我需要一个执行速度快的解决方案。如果有人帮助我，我会很高兴的。谢谢！
尝试这两个查询，而不是for循环：
UPDATE wi_indv_training
INNER JOIN wi_indv_org ON
wi_indv_training.is_deleted=0
AND wi_indv_org.trn_id=wi_indv_training.trn_id
INNER JOIN wi_trn_org ON
wi_indv_org.grp_id=wi_trn_org.grp_id
AND wi_trn_org.is_deleted=0
AND wi_trn_org.trn_id=wi_indv_training.trn_id
SET
wi_indv_training.indv_source = 'Group';
UPDATE wi_indv_training
SET
wi_indv_training.indv_source='Individual'
WHERE
AND wi_indv_training.is_deleted=0
NOT EXISTS (
SELECT ind_id
FROM wi_indv_org
WHERE
wi_indv_org.trn_id = wi_indv_training.trn_id
AND wi_indv_org.ind_id=wi_indv_training.ind_id
LIMIT 1
);
尽可能避免子查询，因为这会减慢查询速度。如果无法避免，至少可以通过限制子查询返回的行数来加快子查询的速度。
您的代码基本上运行：
SELECT COUNT (DISTINCT trn_id) FROM wi_indv_training
查询！如果wi_indv_training表中有200k行，则意味着您正在运行200k个查询，这就解释了为什么它如此缓慢。