> I have a fairly large table (10million rows) with a simple INTEGER
> PRIMARY KEY AUTOINCREMENT field.
>
> Executing 'SELECT max(rowid) FROM MyTable' is very fast, as is
> 'SELECT min(rowid) FROM MyTable'.
>
> However, 'SELECT max(rowid) - min(rowid) FROM MyTable' is slow
> (apparently accessing every row). Further, 'SELECT max(rowid) - 1
> FROM MyTable' is slow - in fact using any constant in this expression
> (including 0) results in a slow query.
>
> Finally, 'SELECT (SELECT max(rowid) FROM MyTable') - 10' is very fast.
>
Check out http://www.sqlite.org/php2004/slides-all.html Page 61
SELECT max(rowid) FROM MyTable
and
SELECT min(rowid) FROM MyTable
are optimized to run without a full table scan. However
SELECT max(rowid) - min(rowid) FROM MyTable
is not, and will perform a full table scan. To achieve the same
functionality with the optimizations, try:
SELECT (SELECT max(rowid) FROM MyTable) - (SELECT min(rowid) FROM MyTable)
SELECT (SELECT max(rowid) FROM MyTable) - 1
- Trey
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
