sql、带有和内部联接的查询优化

Question

我正在尝试优化我的查询，它有一个内部连接和合并。

联接表是一个简单的表，有一个整数字段，我添加了一个唯一的键。

对于where子句，我为这三个字段创建了一个键。

但当我查看计划时，它仍然说它使用了桌面扫描。

我哪里错了？

这是我的查询

select date(a.startdate, '+'||(b.n*a.interval)||' '||a.intervaltype) as due
from billsndeposits a 
inner join util_nums b on date(a.startdate, '+'||(b.n*a.interval)||' 
     '||a.intervaltype) <= coalesce(a.enddate, date('2013-02-26')) 
where not (intervaltype = 'once' or interval = 0) and factid = 1 
order by due, pid;

Answer 1

很可能您的JOIN表达式不能使用任何索引，它是通过进行自然扫描并计算每行的date(a.startdate, '+'||(b.n*a.interval)||' '||a.intervaltype)来计算的。

BTW：这本身就是一个非常奇怪的联接条件。我建议您找到一种更好的方法将billsndeposits连接到util_nums（如果确实需要的话）。

Answer 2

我想我理解你想要实现的目标。但这种连接会导致性能变差。即使删除了日期计算和合并（即将一个日期与另一个日期进行比较），即使有索引，它仍然会很慢（与整数联接相比）。因为你正在动态创建新的日期，所以你无法对它们进行索引。

我建议创建一个包含2列的临时表（1）pid（或您在billsndeposits中使用的任何id）和（2）recurrent_dt

使用以下查询填充新表：

INSERT INTO TEMP
SELECT PID, date(a.startdate, '+'||(b.n*a.interval)||' '||a.intervaltype)
FROM billsndeposits a, util_numbs b;

然后在recurrent_dt列和runstats上创建一个索引。现在，您的select语句可以如下所示：

SELECT recurrence_dt
FROM temp t, billsndeposits a
WHERE t.pid = a.pid
  AND recurrence_dt <= coalesce(a.enddate, date('2013-02-26'))

您可以在这个新表上添加一个expts，然后使临时数据过期。

我知道这会为您的原始查询添加更多的工作，但这是一个有保证的性能改进，并且应该适合频繁运行的脚本。

问候，

编辑

我要做的另一件事是，将enddate默认值设置为date（'2013-02-26'），除非它会影响其他代码和/或没有商业意义。这样你就不必使用coalize了。