SQLite查询优化（子查询和连接）

Question

我可以帮忙翻译。这是一个统计项目的表格。

结构如下：

CREATE TABLE NewStatHistory (
    StatHistoryID uniqueidentifier PRIMARY KEY NOT NULL,
    DateEntered dateTime NOT NULL,
    DateApplies dateTime NOT NULL,
    WhoEnteredID uniqueIdentifier NOT NULL,
    PostingID uniqueIdentifier NULL,
    EnteredValue decimal(19,5) NOT NULL,
    StatID uniqueIdentifier NOT NULL,
    StatStatus int NOT NULL,
    Notes varchar(500) NULL,
    CampusID uniqueidentifier NOT NULL,
    IsTarget bit NOT NULL DEFAULT 0
)

我需要提取每个“DateApplies”最近输入的值。

这个查询在SqlServer中几乎立即运行，但在SQLite中却超时了，我无法找到优化的方法使其正常工作。

SELECT NewStatHistory.* 
FROM NewStatHistory
INNER JOIN (
  SELECT MAX(DateEntered) entered, statID, DateApplies
  FROM NewStatHistory
  WHERE StatID = @statID 
    AND campusID = @campusID
    AND IsTarget = 0
  GROUP BY DateApplies, statID
) summary 
ON summary.entered = newstathistory.dateEntered AND
  summary.statID = newStatHistory.statID AND 
  summary.DateApplies = newStatHistory.DateApplies
WHERE NewStatHistory.StatID = @statID AND
  IsTarget = 0 AND
  campusID = @campusID
ORDER BY NewStatHistory.DateApplies DESC

有人对如何使其工作有任何想法吗？否则，我将不得不找到另一个嵌入式数据库来使用。除了这个查询之外，SQLite已经做了我要求它做的一切。

就索引而言，在SqlServer上，我只索引了主键。在SQLite上，我尝试了仅使用主键，但是加上了其他几列并没有成功。

子查询在SQLite上也可以立即执行，因此似乎是联接导致缓慢。

- Jared

---

你在表中有哪些索引，无论是在查询较快的SqlServer版本还是在超时的SQLite版本中？我怀疑索引的差异可能有助于解释和修复该问题。 - Alex Martelli

Answer 1

运行EXPLAIN QUERY PLAN命令可以发现该查询需要扫描2个表。

您可能希望阅读SQLite查询优化器概述了解优化器的工作原理。

类似以下代码可能有所帮助（未经测试，只是看到它被使用）：

CREATE INDEX foo ON NewStatHistory(statID, campusID, IsTarget, DateApplies);

- Hasturkun

Answer 2

我发现文档中实际上指出这种类型的连接支持不好。解决方法是使用另一个表代替子查询。然后它就可以再次变得非常快了。

- Jared