Situation

I have two sqlit3 database:

newsn3.db --> size: 122.229 kb
- Table: Logs -> [Id: INTEGER, VariableId: INTEGER, Value: REAL, Timestamp: INTEGER]
- Variables: Logs -> [Id: INTEGER, Name: TEXT, Timestamp: INTEGER]
oldsn3.db --> size: 1.800.000 kb
- Table: Logs -> [Id: INTEGER, VariableId: INTEGER, Value: REAL, Timestamp: INTEGER]
- Variables: Logs -> [Id: INTEGER, Name: TEXT, Timestamp: INTEGER]

And in both of them I run a query like this one:

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;

Problem

As amazing as it is, the biggest size database "oldsn3.db" runs the query in just 0.001 seconds. But in the other hand, the small size database "newsn3.db" runs the query in 2,5 seconds.

What could it be ...

I don't really know how to deal with this situation. If someone experienced a similar situation and wants to share it will be fantastic.

EDIT

As @NickW comment I run EXPLAIN QUERY PLAN and this are the outputs:
- "newsn3.db"

id	parent	notused	detail
5	0	0	SCAN Logs
7	0	0	SEARCH Variables USING INTEGER PRIMARY KEY (rowid=?)
10	0	0	SCAN TimestampLookup
26	0	0	USE TEMP B-TREE FOR ORDER BY

"oldsn3.db"

id	parent	notused	detail
6	0	0	SCAN TimestampLookup
8	0	0	SEARCH Logs USING INDEX idx_logs_timestamp (Timestamp>? AND Timestamp<?)
18	0	0	SEARCH Variables USING INTEGER PRIMARY KEY (rowid=?)
30	0	0	USE TEMP B-TREE FOR ORDER BY

New query:

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;

CodePudding user response：

The EXPLAIN results explain. old has an index on the timestamp column (idx_logs_timestamp), new does not, and thus the variation.

Create an index on the timestamp column for the new database.