Hello,
My query wont use the R-Tree index if I provide an ORDER BY clause and
performance degrades considerably. Obviously I can work around with by
making the R-Tree query a subquery and sorting the results, but would
like to know why this is necessary? I've done an VACUUM and ANALYZE.
Is this expected behavior? I haven't found any mention of it in the docs.
The schema is akin to the following:
CREATE TABLE events (
id INTEGER PRIMARY KEY,
cal_id INTEGER NOT NULL, -- This is a FK, ~75 possible values
begins TIMESTAMP NOT NULL, -- unix epoch
ends TIMESTAMP NOT NULL -- unix epoch
);
INSERT INTO events VALUES (null, 123, 1356624000, 1356634800);
INSERT INTO events VALUES (null, 12452, 1356624000, 1356634800);
INSERT INTO events VALUES (null, 34653, 1356624000, 1356634800);
INSERT INTO events VALUES (null, 89287, 1356624000, 1356634800);
-- 55000 rows in events for testing, production will have 500k-1m rows
CREATE VIRTUAL TABLE events_idx USING rtree(id, begins, ends);
INSERT INTO events_idx (id,begins,ends) SELECT id,begins,ends FROM events;
-- Query A, runtime is 300ms+, 8ms without the ORDER BY
SELECT DISTINCT *
FROM events_idx AS ei
INNER JOIN events AS e ON e.id = ei.id
WHERE ei.begins < 1356753600
AND ei.ends > 1356624000
AND e.cal_id IN (123,12452,567,1234,34653,4249,89287)
ORDER BY e.begins, e.ends - e.begins;
-- Query B, runtime is ~8ms
SELECT * FROM
(
SELECT DISTINCT *
FROM events_idx AS ei
INNER JOIN events AS e ON e.id = ei.id
WHERE ei.begins < 1356753600
AND ei.ends > 1356624000
AND e.cal_id IN (123,12452,567,1234,34653,4249,89287)
) AS sub
ORDER BY sub.begins, sub.ends - sub.begins;
Thank you for your time.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
