On 7/10/2013 9:56 PM, Tyler Spivey wrote:
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
SQLite believes that the fastest way is to start by finding
status_timeline_relationship records with timeline_id = 2, then join
those back to status and sort the result.
It seems that ix_status_timeline_relationship_timeline_id index is not
very selective (lots of records with the same timeline_id, many more
than SQLite expects). You may want to drop it. Barring that, you can
disable it for this particular query, by writing
...WHERE +status_timeline_relationship.timeline_id = 2 AND ...
Note the unary plus - it doesn't affect the result, but makes the column
ineligible for indexing.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
