Guy Hindell <[EMAIL PROTECTED]> wrote: > > In promoting SQLite 3 (still v3.2.7, so a bit behind the current > release) for a new project I have been doing some performance tests and > SQLite generally looks very good. However, I have seen one surprising > result. > > My schema is simple, a single table with a simple autoincrement rowid as > the only index. Deleting all the rows from a table populated with > ~600000 rows using simply DELETE FROM Foo takes twice as long as DELETE > FROM Foo WHERE 1. This is odd - > http://sqlite.org/capi3ref.html#sqlite3_changes - would lead me to > expect the opposite. So, I tried DROP TABLE Foo, and sure enough, the > time it takes is the same as the DELETE with no WHERE clause specified - > twice as long! Odd. >
Version 3.3.16 is roughly 10 to 20 times faster than prior versions of SQLite 3 for "DROP TABLE" and "DELETE FROM table" with no WHERE clause. If the speed of these operations is important to you, then you should definitely upgrade. Also note that the AUTOINCREMENT feature of SQLite works a little differently than on MySQL. If all you need is automatically generated IDs that are different from all existing IDs, then AUTOINCREMENT is not necessary and in fact slows down your INSERTs. The AUTOINCREMENT keyword causes SQLite to auto-generate IDs that are both unique over all existing entries, and over all prior entries that have been deleted. Without the AUTOINCREMENT keyword, if you delete a row then create a new row, the new row might reuse the unique ID from a previously deleted row. With AUTOINCREMENT, you are guaranteed that the automatically generated rowid will never have been used before in that table. -- D. Richard Hipp <[EMAIL PROTECTED]> ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
