用计算列排序优化SQLite查询？

Question

我正在使用sqlite查询，以找到一个电机的最佳校准值集使用特定的公式。在最初的查询中，我对计算列进行排序（选择top1），我认为这是最慢的查询。在

我正试图尽快提出这个问题。当前的执行时间大约是0.300秒，我可以通过使用Volume BETWEEN (0.9*1.7006359100341797) AND (1.1*1.7006359100341797)来限制表，并使用tenttable来对计算表（BestMotor）进行排序，从而将其减少到0.150秒。在

我还能做些什么来改善这个问题呢？MotorTable表有大约30000行。不同的汽车供应商大约有55家。在

以下是我最初的疑问： 我试图找到CalX和CalY是最接近指定的体积为每个不同的汽车供应商。在

SELECT T.MotorVendor, ((1/(CalX)+1/(CalY))) AS BestMotor FROM (
SELECT MotorVendor,
       (SELECT CalX
        FROM MotorTable AS T2
        WHERE MotorType = 'text' and T2.MotorVendor = Tools.MotorVendor
        ORDER BY abs(Volume - 1.7006359100341797)
        LIMIT 1
       ) AS CalX,
       (SELECT CalY
        FROM MotorTable AS T2
        WHERE MotorType = 'text' and T2.MotorVendor = Tools.MotorVendor
        ORDER BY abs(Volume - 1.7006359100341797)
        LIMIT 1
       ) AS CalY
FROM (SELECT DISTINCT MotorVendor,
      FROM MotorTable) AS Tools) AS T
      WHERE T.CalX != '' AND T.CalY != ''
      ORDER BY BestMotor DESC
      LIMIT 1;

下面是一个使用tentable对计算列排序的查询（在tentable中该计算列的索引）：

^{pr2}$

更新。。。在

我能把时间缩短到0.05秒。。。 我创建了一个表来存储所有不同的MotorVendor，并将主查询改为：

^{3}$

下面是我创建的两个表和索引的模式。在

CREATE TABLE MotorTable (
    CalY    real,
    CalX    real,
    Volume  real,
    MotorType   text,
    MotorVendor text
);
CREATE TABLE TempMotorVendorTable (
    MotorVendor TEXT
);
CREATE INDEX `MotorVendorIndex` ON MotorTable (`MotorVendor` ASC)
CREATE INDEX VolumeIndex ON MotorTable (Volume DESC)

解释查询计划输出

"0" "0" "0" "SCAN TABLE TempMotorVendorTable AS Tools"
"0" "1" "1" "SEARCH TABLE MotorTable USING AUTOMATIC COVERING INDEX (MotorVendor=?)"
"0" "0" "0" "EXECUTE CORRELATED SCALAR SUBQUERY 1"
"1" "0" "0" "SEARCH TABLE MotorTable AS T2 USING INDEX CompToolIndex (MotorVendor=?)"
"1" "0" "0" "USE TEMP B-TREE FOR ORDER BY"
"0" "0" "0" "EXECUTE CORRELATED SCALAR SUBQUERY 2"
"2" "0" "0" "SEARCH TABLE MotorTable AS T3 USING INDEX CompToolIndex (MotorVendor=?)"
"2" "0" "0" "USE TEMP B-TREE FOR ORDER BY"
"0" "0" "0" "USE TEMP B-TREE FOR ORDER BY"

Answer 1

ORDER BY abs(Volume - 1.234)无法使用索引进行优化。 （甚至在下一个版本的SQLite中也是如此，因为1.234值不是常量，它将有表达式索引。）

但是，实际上并不想对所有行进行排序，只需要最近的一行。 这可以通过一个不同的查询来完成，它搜索该值下面的第一行和该值上面的第一行（这些搜索和排序可以使用索引）。然后取这两行中最近的一行（只排序两行比较快）：

(SELECT CalX
 FROM (SELECT CalX, diff
       FROM (  get the largest value at or below 1.234
             SELECT CalX, abs(Volume - 1.234) AS diff
             FROM MotorTable AS T2
             WHERE MotorType = 'text'
               AND MotorVendor = Tools.MotorVendor 
               AND Volume <= 1.234
             ORDER BY Volume DESC
             LIMIT 1)
       UNION ALL
       SELECT CalX, diff
       FROM (  get the smallest value above 1.234
             SELECT CalX, abs(Volume - 1.234) AS diff
             FROM MotorTable AS T2
             WHERE MotorType = 'text'
               AND MotorVendor = Tools.MotorVendor 
               AND Volume > 1.234
             ORDER BY Volume ASC
             LIMIT 1)
       ORDER BY diff
       LIMIT 1)
)

要使MotorTable中的搜索有效，需要对所有三列使用一个索引，并且不等式比较必须在最后一列上：

^{pr2}$