Re: [sqlite] deleting 100,000 entries
Actually, I seem to recall a discussion that revealed that "DELETE FROM Foo" *does* truncate the table. The discussion came up because someone complained that "ON DELETE" triggers were not being fired; the explanation was that 'DELETE FROM Foo" simply drops and re-creates Foo. The solution was to rewrite the code as "DELETE FROM Foo WHERE 1=1" or something similar. This was pretty easy to verify: sqlite> explain delete from foo; addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 0 explain delete from foo; 00 1 Goto 0 4 000 2 Clear 2 0 0 foo00 3 Halt 0 0 000 4 Transaction0 1 000 5 VerifyCookie 0 1 000 6 Goto 0 2 000 sqlite> explain delete from foo where 1=1; addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 0 explain delete from foo where 1=1; 00 1 Goto 0 23000 2 Integer1 2 000 3 Integer1 3 000 4 Ne 3 1426a 5 OpenRead 0 2 000 6 SetNumColumns 0 0 000 7 Rewind 0 14000 8 Integer1 3 000 9 Integer1 2 000 10Ne 2 1336a 11Rowid 0 1 000 12FifoWrite 1 0 000 13Next 0 8 000 14Close 0 0 000 15OpenWrite 0 2 000 16SetNumColumns 0 2 000 17FifoRead 1 21000 18NotExists 0 20100 19Delete 0 1 0 foo00 20Goto 0 17000 21Close 0 0 000 22Halt 0 0 000 23Transaction0 1 000 24VerifyCookie 0 1 000 25TableLock -12 0 foo00 26Goto 0 2 000 Heading over to http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/vdbe.c&v=1.741 and looking at OP_Clear reveals that "DELETE FROM Foo" is closest in implementation to "TRUNCATE TABLE Foo". On Wed, May 21, 2008 at 2:46 AM, Harold Wood & Meyuni Gani < [EMAIL PROTECTED]> wrote: > Doesn't sqlite support the truncate table command > > Woody > from his pda > > -Original Message- > From: Carlo S. Marcelo <[EMAIL PROTECTED]> > Sent: Tuesday, May 20, 2008 8:49 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] deleting 100,000 entries > > @Barefoot and Keith, > > Awesome! It took forever for the web interface to delete, and I had to > restart httpd for the database to unlock.. the command you provided took > less than a second to clear everything out, thanks! > > Carlo > > - Original Message ---- > From: BareFeet <[EMAIL PROTECTED]> > To: General Discussion of SQLite Database > Sent: Wednesday, May 21, 2008 11:45:06 AM > Subject: Re: [sqlite] deleting 100,000 entries > > Hi Carlo, > > > I want to clear the whole thing out(list)! > > Do you mean that you want to delete all rows from the "list" table? If > so, do this: > > delete from list; > > See the syntax pages at: > http://www.sqlite.org/lang.html > http://www.sqlite.org/lang_delete.html > > Tom > BareFeet > http://www.tandb.com.au/sqlite/compare/?ml > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] deleting 100,000 entries
The command "Delete from table" is optimized into a "drop table" then "Create table" pair. Effectively making the delete from table a "truncate". http://sqlite.org/lang_delete.html HTH, Ken Harold Wood & Meyuni Gani <[EMAIL PROTECTED]> wrote: Doesn't sqlite support the truncate table command Woody from his pda -Original Message- From: Carlo S. Marcelo Sent: Tuesday, May 20, 2008 8:49 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] deleting 100,000 entries @Barefoot and Keith, Awesome! It took forever for the web interface to delete, and I had to restart httpd for the database to unlock.. the command you provided took less than a second to clear everything out, thanks! Carlo - Original Message From: BareFeet To: General Discussion of SQLite Database Sent: Wednesday, May 21, 2008 11:45:06 AM Subject: Re: [sqlite] deleting 100,000 entries Hi Carlo, > I want to clear the whole thing out(list)! Do you mean that you want to delete all rows from the "list" table? If so, do this: delete from list; See the syntax pages at: http://www.sqlite.org/lang.html http://www.sqlite.org/lang_delete.html Tom BareFeet http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] deleting 100,000 entries
Doesn't sqlite support the truncate table command Woody from his pda -Original Message- From: Carlo S. Marcelo <[EMAIL PROTECTED]> Sent: Tuesday, May 20, 2008 8:49 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] deleting 100,000 entries @Barefoot and Keith, Awesome! It took forever for the web interface to delete, and I had to restart httpd for the database to unlock.. the command you provided took less than a second to clear everything out, thanks! Carlo - Original Message From: BareFeet <[EMAIL PROTECTED]> To: General Discussion of SQLite Database Sent: Wednesday, May 21, 2008 11:45:06 AM Subject: Re: [sqlite] deleting 100,000 entries Hi Carlo, > I want to clear the whole thing out(list)! Do you mean that you want to delete all rows from the "list" table? If so, do this: delete from list; See the syntax pages at: http://www.sqlite.org/lang.html http://www.sqlite.org/lang_delete.html Tom BareFeet http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] deleting 100,000 entries
Thanks Keith. Carlo - Original Message From: Keith Goodman <[EMAIL PROTECTED]> To: General Discussion of SQLite Database Sent: Wednesday, May 21, 2008 12:45:42 PM Subject: Re: [sqlite] deleting 100,000 entries On Tue, May 20, 2008 at 8:59 PM, Carlo S. Marcelo <[EMAIL PROTECTED]> wrote: > That means I save space right? Because the space all the original entries are > still intact when I run the delete command? Whereas dropping table clears > everything (since we are re-creating it)? > > I'm only assuming this is what dropping table does. > > It would be cool to know how to drop tables thanks Gerry. sqlite> drop list; Dropping the table does not save space by default. Take a look at the vacuum command: http://www.sqlite.org/lang_vacuum.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] deleting 100,000 entries
On Tue, May 20, 2008 at 8:59 PM, Carlo S. Marcelo <[EMAIL PROTECTED]> wrote: > That means I save space right? Because the space all the original entries are > still intact when I run the delete command? Whereas dropping table clears > everything (since we are re-creating it)? > > I'm only assuming this is what dropping table does. > > It would be cool to know how to drop tables thanks Gerry. sqlite> drop list; Dropping the table does not save space by default. Take a look at the vacuum command: http://www.sqlite.org/lang_vacuum.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] deleting 100,000 entries
That means I save space right? Because the space all the original entries are still intact when I run the delete command? Whereas dropping table clears everything (since we are re-creating it)? I'm only assuming this is what dropping table does. It would be cool to know how to drop tables thanks Gerry. Carlo - Original Message From: Gerry Snyder <[EMAIL PROTECTED]> To: General Discussion of SQLite Database Sent: Wednesday, May 21, 2008 11:54:45 AM Subject: Re: [sqlite] deleting 100,000 entries BareFeet wrote: > Hi Carlo, > > >> I want to clear the whole thing out(list)! >> > > Do you mean that you want to delete all rows from the "list" table? If > so, do this: > > delete from list; Or you could drop the table and then create it again. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] deleting 100,000 entries
BareFeet wrote: > Hi Carlo, > > >> I want to clear the whole thing out(list)! >> > > Do you mean that you want to delete all rows from the "list" table? If > so, do this: > > delete from list; Or you could drop the table and then create it again. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] deleting 100,000 entries
@Barefoot and Keith, Awesome! It took forever for the web interface to delete, and I had to restart httpd for the database to unlock.. the command you provided took less than a second to clear everything out, thanks! Carlo - Original Message From: BareFeet <[EMAIL PROTECTED]> To: General Discussion of SQLite Database Sent: Wednesday, May 21, 2008 11:45:06 AM Subject: Re: [sqlite] deleting 100,000 entries Hi Carlo, > I want to clear the whole thing out(list)! Do you mean that you want to delete all rows from the "list" table? If so, do this: delete from list; See the syntax pages at: http://www.sqlite.org/lang.html http://www.sqlite.org/lang_delete.html Tom BareFeet http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] deleting 100,000 entries
Hi Carlo, > I want to clear the whole thing out(list)! Do you mean that you want to delete all rows from the "list" table? If so, do this: delete from list; See the syntax pages at: http://www.sqlite.org/lang.html http://www.sqlite.org/lang_delete.html Tom BareFeet http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] deleting 100,000 entries
On Tue, May 20, 2008 at 8:40 PM, Carlo S. Marcelo <[EMAIL PROTECTED]> wrote: > I run select * from list; and get loads and loads of entries: > > > 81203|0|1|www.newwhitelistentry44994.com|0|0|0 > 81204|0|1|www.newwhitelistentry44995.com|0|0|0 > 81205|0|1|www.newwhitelistentry44996.com|0|0|0 > 81206|0|1|www.newwhitelistentry44997.com|0|0|0 > 81207|0|1|www.newwhitelistentry44998.com|0|0|0 > 81208|0|1|www.newwhitelistentry44999.com|0|0|0 > 81209|0|1|www.newwhitelistentry45000.com|0|0|0 > > > I want to clear the whole thing out(list)! To remove *all* records from the table: sqlite> delete from list; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
