需要加快不存在查询的地方

Question

我相信这会导致5分钟到20分钟的延迟，具体取决于记录数量。我需要将其转换为左联接，但需要一些帮助。

qry_arr = array(':bill_type' => "INT");
$sql = "update ".$billing_table." c set c.bill_type = :bill_type";
$sql .= " WHERE  NOT EXISTS (SELECT s.abbreviation FROM   state s WHERE  s.abbreviation = c.out_location)";
$sql .= " and c.out_location != 'UNKNOWN' and c.out_location != ''";

Answer 1

UPDATE $billing_table c
LEFT JOIN state s ON s.abbreviation = c.out_location
SET c.bill_type = :bill_type
WHERE s.abbreviation IS NULL
AND c.out_location NOT IN ('UNKNOWN', '')

这与不匹配的行的SELECT的语法基本相同。仅在不存在值时，请参见返回行。只需将SELECT ... FROM替换为UPDATE，然后在WHERE之前插入SET子句。

确保您在out_location和abbreviation上有索引。