On 13/11/2011 3:37 a.m., Igor Tandetnik wrote:
Josh Gibbs<jgi...@imailds.com> wrote:
The timestamps are already integers. We stumbled across that CAST operation
optimization purely by accident. I don't remember what led to it, but
we found
that it gave a measurable performance boost casting the integer as an
integer.
This works by *suppressing* the index. When you are selecting a significant
portion of all the records in the table, a linear scan works faster than an
extra level of indirection through the index. But SQLite doesn't know
beforehand how many records migh end up being retrieved, and calculates the
query plan based largely on syntactical structure of the query (but see ANALYZE
command).
You can make it
WHERE +messages.TimeStamp BETWEEN 0 AND 9999999999
The unary plus is a no-op except it prevents SQLite from using an index on the
column.
Very interesting, thanks. I'd asked on the list about this when we
first came across it but no one ever responded to the question.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
