Removing lots of data from a DB table
If you need to remove large amounts of data from a table ( specially when you need to remove *all* the data in a table), remember its faster to drop the table, and recreate it rather than doing a 'delete from tablename;'.
eg: this is against a postgresql db:
drop table rawdata; Response time: 0m 2s 433ms Total time: 0m 2s 433ms CREATE TABLE "rawdata" ("ip" char(33), "sdate" varchar(20) NOT NULL, "key" varchar(100) NOT NULL,"country" varchar(2), "region" varchar(2), "city" varchar(40), "machine" int); Response time: 0m 0s 337ms Total time: 0m 0s 337ms
delete from rawdata; 200000000 row(s) affected Response time: 0m 15s 353ms Total time: 0m 15s 353ms
In some cases, where you want to drop most ( eg > 80% ) of the data in the table, its faster to select the data to be retained into a temp table, drop the original table and rebuild the temp table to the original name.
'truncate' works quite nicely too. Thanks for pointing that out - however I've had issues with truncate on mysql db's when there were external references / foreign keys.