Mysql 3表2连接

Question

目标是更新order_items表，然后更新列transportcost。

Table test.inserttest2
- Rows: 480 000
 - Order table
 - Column: Ordernumber and Country


Table test.inserttest3
- Rows: 2 600 000
 - Order items
 - Column: Ordernumber, SKU and Transportcost

Table transportcost
- Rows: 17 000
- a global list of transport price
- Column: SKU, Country, Transportprice

在执行update语句之前，我执行select语句检查是否得到正确的字段：-64秒-行:62,000

SELECT
  a.ordernumber,
  b.country,
  b.transportprice
FROM test.inserttest3 a
INNER JOIN transportcost AS b
  ON a.sku = b.sku
WHERE a.system_created > '2017-12-01'

我得到了一个这样的名单：

17004706    Germany 3.00
17004706    France  6.00
17004706    Netherlands 3.00
17004706    Belgium 3.00
17004706    Austria 4.00
17004706    Italy   5.00
17004706    Switzerland 1.00
17004706    UK  8.00
17004706    Spain   4.00
17004706    Poland  4.00
17004706    Norway  9.00
17004706    Denmark 5.00
17004706    Sweden  6.00
17004706    Czech   4.00
17004706    Finland 6.00
17004706    Ireland 3.00

下一步是将此信息用于order_items表。这里出了问题，这是我尝试过的，但在运行这些查询8小时后，我停止了它们。

SELECT
  ps2.ordernumber,
  ps2.sku,
  ps2.country,
  ps2.transportprice
FROM test.inserttest2 aa
JOIN (SELECT
  a.ordernumber,
  b.sku,
  b.country,
  b.transportprice
FROM test.inserttest3 a
INNER JOIN transportcost AS b
  ON a.sku = b.sku
WHERE a.system_created > '2017-12-01') ps2
  ON aa.odernumber = ps2.ordernumber
WHERE aa.country = ps2.country
AND aa.system_created > '2017-12-01'
;

。

SELECT
  b.sku,
  b.country,
  b.transportcost
FROM test.inserttest3 AS a
INNER JOIN test.inserttest2 AS aa
  ON aa.ordernumber = a.ordernumber
INNER JOIN transportcost AS b
  ON a.sku = b.sku
WHERE aa.country = b.country
AND a.system_created > '2017-12-01'

;

Answer 1

1. 您希望用inserttest3表的传输价格更新您调用的transportcost订单表。要使这种情况发生，首先尝试一个查询。听起来很简单:只要加入表格就行了。
2. 您希望将此限制为在2017-12-01之后创建的订单和订单。使用适当的数据库，不可能在订单之前插入订单项，因此我们可以将其限制在2017-12-01之后创建的订单上。

查询：

select
  io.ordernumber,
  io.sku,
  io.transportcost as cost_wrong,
  t.transportprice as cost_right
from test.inserttest2 o 
join test.inserttest3 io on io.ordernumber = o.ordernumber
join transportcost t on t.sku = io.sku and t.country = o.country
where o.system_created > date '2017-12-01';

因此，日期是限制要读取的数据量的唯一参数。因此，您应该在这个列上有一个索引。可能的索引：

create index idx_o on inserttest2 (system_created, ordernumber, country);
create index idx_io on inserttest3 (ordernumber, sku);
create index idx_t on transportcost (sku, country, transportprice);