SQLite3同结构数据库查询耗时差异异常问题求助

我有两个SQLite3数据库：

• newsn3.db --> 大小：122.229 KB
  • 表：Logs --> [Id: 整数型, VariableId: 整数型, Value: 实数型, Timestamp: 整数型]
  • 表：Variables --> [Id: 整数型, Name: 文本型, Timestamp: 整数型]
• oldsn3.db --> 大小：1,800,000 KB
  • 表：Logs --> [Id: 整数型, VariableId: 整数型, Value: 实数型, Timestamp: 整数型]
  • 表：Variables --> [Id: 整数型, Name: 文本型, Timestamp: 整数型]

我在两个数据库中执行了相同的查询语句：

CREATE TEMPORARY TABLE TimestampLookup (
  TimestampStart bigint,
  TimestampEnd bigint,
  Category varchar(10)
);


INSERT INTO TimestampLookup (TimestampStart, TimestampEnd, Category)
VALUES
--(638089367345448192, 638089367391931136, '1-1'),
--(638089368588500608, 638089368635085696, '2-1'),
--(638089423052093952, 638089423097359360, '3-1'),
--(638089424602983168, 638089424646700416, '4-1'),
--(638089426835122560, 638089426880267392, '5-1'),
--(638091321516003456, 638091321616836992, '6-1'),
--(638091346537689472, 638091346646865280, '7-1'),
(638108447105669629, 638108447105669851, '8-1');


SELECT Variables.Name, Logs.Timestamp, Logs.Value, TimestampLookup.Category
FROM Logs
JOIN Variables ON Logs.VariableId = Variables.Id
JOIN TimestampLookup ON Logs.Timestamp BETWEEN TimestampLookup.TimestampStart AND TimestampLookup.TimestampEnd
ORDER BY TimestampLookup.Category ASC, Logs.Timestamp ASC;

奇怪的是，体积更大的oldsn3.db执行该查询仅需0.001秒，而体积更小的newsn3.db执行同一查询却耗时2.5秒。

• 我不知道该怎么处理这种情况，希望有类似经验的用户能分享解决方案，万分感谢。

补充信息 #

根据@NickW的建议，我执行了EXPLAIN QUERY PLAN，结果如下：

• newsn3.db的执行计划

• oldsn3.db的执行计划

用于执行计划分析的新增查询语句：

CREATE TEMPORARY TABLE TimestampLookup (
  TimestampStart bigint,
  TimestampEnd bigint,
  Category varchar(10)
);


INSERT INTO TimestampLookup (TimestampStart, TimestampEnd, Category)
VALUES
--(638089367345448192, 638089367391931136, '1-1'),
--(638089368588500608, 638089368635085696, '2-1'),
--(638089423052093952, 638089423097359360, '3-1'),
--(638089424602983168, 638089424646700416, '4-1'),
--(638089426835122560, 638089426880267392, '5-1'),
--(638091321516003456, 638091321616836992, '6-1'),
--(638091346537689472, 638091346646865280, '7-1'),
(638089314046750888, 638089314046751656, '8-1');


EXPLAIN QUERY PLAN SELECT Variables.Name, Logs.Timestamp, Logs.Value, TimestampLookup.Category
FROM Logs
JOIN Variables ON Logs.VariableId = Variables.Id
JOIN TimestampLookup ON Logs.Timestamp BETWEEN TimestampLookup.TimestampStart AND TimestampLookup.TimestampEnd
ORDER BY TimestampLookup.Category ASC, Logs.Timestamp ASC;

内容的提问来源于stack exchange，提问作者Xispa