SQLite: Optimize table scans
In my table I have some columns that have no index on them, searching for a value in those columns can take very long because SQLite does a full table scan.
In my specific case the row I'm looking for (the values are unique) is almost always among the most recently inserted.
I suspect that SQLite starts from the oldest (first) row when doing the scan, is there any way to instruct SQLite to do the table-scan in reverse order?
UPDATE: I found this in the 开发者_开发技巧changelog:
The optimizer will now scan tables in the reverse if doing so will satisfy an ORDER BY ... DESC clause.
So maybe this means I can just do add an ORDER BY clause, to speed it up.
The solution was:
ORDER BY rowid DESC LIMIT 1
It made the lookups lightning fast!
The order of the scan (oldest-to-youngest or youngest-to-oldest) is irrelevant because a FULL table scan is required. Every row must be visited. Although you mention 'the row I'm looking for' the condition
.... where col = 'a'
might return one row, or it might return 10 rows, or 500 rows. SQLite cannot simply stop and call it a good day's work when the first matching row is encountered unless you use the LIMIT directive.
EDIT: What you could do, however, is use a timestamp column, index it, and then use an inline view to get the relatively recent rows:
select * from
(
select * from T
where datecreated > {somerecentdate}
) as myView
where myView.someColumn = 'a'
or simply
select * from T where datecreated > {some date} and somecolumn = 'a'
That approach could be an iterative process -- if no rows are returned you may need to requery with a wider time-window. But if you're going to index datecreated you might as well index [someColumn].
精彩评论