这个SQL内部联接的性能能得到改善吗？

Question

我正在使用python和sqllite3，我想知道这个查询的性能是否可以提高？

具有100,000行的main表

    0   1   2   3   4   Amount
0   0   9   12  6   60  40800.0
1   0   9   12  6   61  40100.0
2   0   9   12  6   65  39900.0
3   0   9   12  6   74  40300.0
4   0   9   12  7   60  40600.0

util表~75,000行

    0   1   2   Amount
0   78  75  65  9900.0
1   80  75  65  9900.0
2   80  72  65  10000.0
3   78  72  65  10000.0
4   79  75  65  10000.0

该查询当前获取两个表的笛卡儿积，其中金额之和在49,700至50,000之间，如果我的理解正确，则得到前200,000匹配。

con = sqlite3.connect(':memory:')
df.to_sql(name='main', con=con)
df1.to_sql(name='util', con=con)

query = '''
SELECT *
FROM main AS m
INNER JOIN
util AS u
ON
  50000 >= m.Amount + u.Amount
AND
  49700 <= m.Amount + u.Amount
LIMIT
  200000;
'''
final_df = pd.read_sql_query(query, con)

Answer 1

由于列值不是匹配的，但是表达式m.Amount + u.Amount，上的必须为两个表之间的每个可能的行组合(100 k* 75k =7500百万或75亿个组合体)计算。实际上，您得到的是交叉连接，因为在两个表之间的任何列上都没有匹配。

1.您可以确保表达式只计算一次，而不是通过使用BETWEEN操作符对50000 >= m.Amount + u.Amount & 49700 <= m.Amount + u.Amount子句的每个部分进行计算。为了清晰起见，我只想使用table1，table2和WHERE的标准：

SELECT * FROM main AS m
INNER JOIN
util AS u
ON
  m.Amount + u.Amount BETWEEN 49700 AND 50000
;

2. --您必须使用其他方法来减少选中的行数。例如，当两个表的Amount都超过50,000时，它就不能匹配，因此在验证的早期就会被排除在外，甚至一次也不计算m.Amount + u.Amount，从而节省了时间：

SELECT * FROM main AS m, util AS u
WHERE
  m.Amount <= 50000
AND
  u.Amount <= 50000
AND
  m.Amount + u.Amount BETWEEN 49700 AND 50000
;

如果金额不能为0，则将<= 50000更改为< 50000。

3.您可以做其他事情，比如在每个表中找到最小数量，然后确保另一个表的数量小于50000 - that first min amt。

4.使用“2个数之和”问题，您可以为其中一个表一次性计算最小匹配Amt和最大匹配Amt (添加两个新列)，然后使用另一个表的Amt进行中间检查。它仍然需要做一个交叉连接，但cpu时间，以评估每一个匹配是减少的。

ALTER TABLE main ADD COLUMN min_match INT default 0;
ALTER TABLE main ADD COLUMN max_match INT default 0;
UPDATE main SET min_match = 49700 - Amount,
                max_match = 50000 - Amount;

SELECT * FROM main AS m, util AS u
WHERE
  u.Amount BETWEEN m.min_match AND m.max_match
;