我有一个查询性能的问题,找不到如何解决它。
两个表都有20k行
table 1. prod_id, month, year, volume
1. 1 5 2020 4
2. 2 5 2020 5
...
table 2. prod_id, wh, share, month, year
1. 1 1 10% 5 2020
2. 1 2 10% 5 2020
3. 1 3 40% 5 2020
4. 1 4 40% 5 2020
...Prod_id喜欢索引,但不喜欢主键,因为表1有不同月份、年份等的重复项,我需要这样获取表(table1 * wh = 80k行):
table 3. prod_id, wh, month, year, new_volume
1. 1 1 5 2020 volume*share
2. 1 2 5 2020
3. 1 3 5 2020
4. 1 4 5 2020
5. 2 1 5 2020
6. 2 2 5 2020
...我使用了LEFT JOIN来做这件事,但是花费了太长的15+分钟,你有什么想法来改进这个查询吗?
SELECT table1.prod_id, table2.wh, table1.month, table1.year, volume*share AS new_volume
FROM table1
LEFT JOIN table2 ON (table1.prod_id = table2.prod_id) AND (table1.month = table2.month) AND (table1.year = table2.year)发布于 2020-10-19 18:54:40
对于此查询:
SELECT table1.prod_id, table2.wh, table1.month, table1.year, volume*share AS new_volume
FROM table1 LEFT JOIN
table2
ON (table1.prod_id = table2.prod_id) AND
(table1.month = table2.month) AND
(table1.year = table2.year)您想要一个关于table2的索引
create index idx_table2_prod_month_year on table2(prod_id, month, year)您还可以在select中包含来自table2的wh2和任何其他列。
https://stackoverflow.com/questions/64425873
复制相似问题