I'm trying to speed up this query, and don't understand why it's not using
ix_status_created_at_sort.
created_at_sort is a sorted column I'm using as part of a scrolling cursor for
moving forward/backward through results,
and the status table has ~36000 rows.
SELECT status.text
FROM status
JOIN status_timeline_relationship ON status.id =
status_timeline_relationship.status_id
WHERE
status_timeline_relationship.timeline_id = 2 AND
status.created_at_sort > 123449400901
ORDER BY status.created_at_sort
LIMIT 1 OFFSET 0;
The output of explain query plan:
0|0|1|SEARCH TABLE status_timeline_relationship USING INDEX
ix_status_timeline_relationship_timeline_id (timeline_id=?) (~10 rows)
0|1|0|SEARCH TABLE status USING INDEX ix_status_id (id=?) (~3 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY
This query runs in about 0.6S.
And the schema:
CREATE TABLE status (
db_id INTEGER NOT NULL,
created_at INTEGER,
created_at_sort INTEGER,
id INTEGER,
coordinates VARCHAR,
favorite_count INTEGER,
favorited BOOLEAN,
filter_level VARCHAR,
geo BOOLEAN,
in_reply_to_screen_name VARCHAR,
in_reply_to_status_id INTEGER,
in_reply_to_user_id INTEGER,
lang VARCHAR,
possibly_sensitive BOOLEAN,
retweet_count INTEGER,
retweeted BOOLEAN,
source VARCHAR,
truncated BOOLEAN,
text VARCHAR(160),
embedded_id INTEGER,
user_id INTEGER,
embedded_ INTEGER,
PRIMARY KEY (db_id),
CHECK (favorited IN (0, 1)),
CHECK (geo IN (0, 1)),
CHECK (possibly_sensitive IN (0, 1)),
CHECK (retweeted IN (0, 1)),
CHECK (truncated IN (0, 1)),
FOREIGN KEY(user_id) REFERENCES user (db_id),
FOREIGN KEY(embedded_) REFERENCES status (db_id)
);
CREATE INDEX ix_status_created_at ON status (created_at);
CREATE INDEX ix_status_created_at_sort ON status (created_at_sort);
CREATE INDEX ix_status_id ON status (id);
CREATE INDEX ix_status_in_reply_to_status_id ON status (in_reply_to_status_id);
CREATE INDEX ix_status_text ON status (text);
CREATE TABLE status_timeline_relationship (
db_id INTEGER NOT NULL,
status_id INTEGER,
timeline_id INTEGER,
PRIMARY KEY (db_id),
FOREIGN KEY(status_id) REFERENCES status (id),
FOREIGN KEY(timeline_id) REFERENCES status_timeline (db_id)
);
CREATE INDEX ix_status_timeline_relationship_timeline_id ON
status_timeline_relationship (timeline_id);
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
