I have the following schema (slightly simplified for this post)
CREATE TABLE projects (
project_id INTEGER PRIMARY KEY,
project_start DATETIME
);
CREATE TABLE feeds (
feed_id INTEGER PRIMARY KEY,
feed_uri TEXT,
project_id INTEGER
);
CREATE TABLE feed_history (
feed_history_id INTEGER PRIMARY KEY,
feed_id INTEGER
);
CREATE TABLE uris (
uri_id INTEGER PRIMARY KEY,
uri_content TEXT,
downloaded_on DATETIME DEFAULT CURRENT_TIMESTAMP,
feed_history_id INTEGER
);
CREATE VIRTUAL TABLE fts_uri USING fts4 (uri_id, uri_content);
The db file is about 3 GB, with 79 entries in the `feeds` table and 6847
entries in the `uris` table. The following query takes way too long --
SELECT u.uri_id uri_id, u.uri uri, u.downloaded_on
FROM fts_uri f
JOIN uris u ON f.uri_id = u.uri_id
JOIN feed_history fh ON u.feed_history_id = fh.feed_history_id
JOIN feeds f ON f.feed_id = f.feed_id
JOIN projects p ON f.project_id = p.project_id
WHERE p.project_id = 3
AND Datetime(u.downloaded_on) >= Datetime(p.project_start)
AND fts_uri MATCH ?
ORDER BY u.uri_id, downloaded_on DESC;
EXPLAIN on the above query is as below, but I don't know how to read EXPLAIN's
output. Could one of you suggest on what index I might have to make/use to
speed up the query?
0|Trace|0|0|0||00|
1|OpenEphemeral|5|4|0|keyinfo(2,BINARY,-BINARY)|00|
2|Integer|3|1|0||00|
3|Goto|0|58|0||00|
4|VOpen|0|0|0|vtab:7FF44A407B38:10D01E5C0|00|
5|OpenRead|1|9|0|6|00|
6|OpenRead|2|6|0|0|00|
7|OpenRead|4|2|0|6|00|
8|OpenRead|3|3|0|3|00|
9|String8|0|4|0|education|00|
10|Integer|4|2|0||00|
11|Integer|1|3|0||00|
12|VFilter|0|43|2||00|
13|VColumn|0|0|6||00|
14|MustBeInt|6|42|0||00|
15|NotExists|1|42|6||00|
16|Column|1|5|7||00|
17|MustBeInt|7|42|0||00|
18|NotExists|2|42|7||00|
19|MustBeInt|1|42|0||00|
20|NotExists|4|42|1||00|
21|Column|1|4|2||00|
22|Function|0|2|9|datetime(-1)|01|
23|Column|4|5|3||00|
24|Function|0|3|10|datetime(-1)|01|
25|Lt|10|42|9||6a|
26|Rewind|3|42|0||00|
27|Rowid|3|8|0||00|
28|Ne|8|41|8||6b|
29|Column|3|2|10||00|
30|Ne|1|41|10|collseq(BINARY)|6b|
31|Rowid|1|11|0||00|
32|Column|1|1|12||00|
33|Column|1|4|13||00|
34|MakeRecord|11|3|10||00|
35|Rowid|1|14|0||00|
36|Column|1|4|15||00|
37|Sequence|5|16|0||00|
38|Move|10|17|1||00|
39|MakeRecord|14|4|8||00|
40|IdxInsert|5|8|0||00|
41|Next|3|27|0||01|
42|VNext|0|13|0||00|
43|Close|0|0|0||00|
44|Close|1|0|0||00|
45|Close|2|0|0||00|
46|Close|4|0|0||00|
47|Close|3|0|0||00|
48|OpenPseudo|6|10|3||00|
49|Sort|5|56|0||00|
50|Column|5|3|10||00|
51|Column|6|0|11||20|
52|Column|6|1|12||00|
53|Column|6|2|13||00|
54|ResultRow|11|3|0||00|
55|Next|5|50|0||00|
56|Close|6|0|0||00|
57|Halt|0|0|0||00|
58|Transaction|0|0|0||00|
59|VerifyCookie|0|21|0||00|
60|TableLock|0|9|0|uris|00|
61|TableLock|0|6|0|feed_history|00|
62|TableLock|0|2|0|projects|00|
63|TableLock|0|3|0|feeds|00|
64|Goto|0|4|0||00|
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
