On Dec 18, 2008, at 4:47 AM, Daniel Witte wrote:
> So, I'd like to use the statement
>
> DELETE FROM moz_cookies WHERE lastAccessed <= ?1 ORDER BY
> lastAccessed ASC
> LIMIT ?2
>
> for a query in Firefox backend code (we use the shipped
> amalgamation, which
> doesn't allow SQLITE_ENABLE_UPDATE_DELETE_LIMIT). The closest query
> we can
> implement, as far as I can tell, would be (note id is the pkey):
>
> DELETE FROM moz_cookies WHERE id IN (SELECT id FROM moz_cookies WHERE
> lastAccessed
> <= ?1 ORDER BY lastAccessed ASC LIMIT ?2)
>
> Will sqlite optimize the latter to the former? If not, what are the
> likely
> perf differences here? (And why isn't
> SQLITE_ENABLE_UPDATE_DELETE_LIMIT
> enabled by default?)
Unless 'id' is already an INTEGER PRIMARY KEY, it would be slightly
better
to use rowid than 'id'. i.e. do:
DELETE FROM moz_cookies WHERE rowid IN (
SELECT rowid
FROM moz_cookies
WHERE lastAccessed <= ?1
ORDER BY lastAccessed
LIMIT ?2
);
The special syntax enabled by SQLITE_ENABLE_UPDATE_DELETE_LIMIT is
basically
implemented by transforming queries like your first example to the
above. So
you should get exactly the same performance if you do the transform
yourself
by hand before passing the query to SQLite.
I think ENABLE_UPDATE_DELETE_LIMIT is not enabled by default because
it is
non-standard SQL. And because the policy is to keep the binary footprint
as small as possible.
Dan.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
