Re: Deleting all from all tables in MYSQL

If you have referential integrity on your database it might slow things down a lot. Consider dropping the database and re-creating it, perhaps by executing an external script which could be easily changed. Perhaps you do not actually need Perl code to do this.

Comment on Re: Deleting all from all tables in MYSQL

Replies are listed 'Best First'.
Re^2: Deleting all from all tables in MYSQL by justin423 (Scribe) on Sep 19, 2012 at 19:24 UTC
it is a huge database and just for testing purposes, so speed isn't a concern, because everytime it runs, it still takes about 2 hours to run.	[reply]
Re^3: Deleting all from all tables in MYSQL by Tux (Canon) on Sep 20, 2012 at 06:55 UTC
In that case, dropping the database and recreating it is most likely the fastest solution. Database drops in PostgreSQL and MySQL/MariaDB are almost instant. In Oracle it takes ages to (re)create a database. To get all tables, why not use the DBI::tables? That way you don't have to dig into database specific calls. If you still want to delete in stead of re-create, why use a `prepare/execute` combo instead of just do? `$dbh->do ("truncate table $_") for $dbh->tables (undef, undef, undef, "TABLE");` or `$dbh->do ("delete from $_") for $dbh->tables (undef, undef, undef, "TABLE");` In other databases, you could use `delete from table cascade`, but iirc that syntax is not supported by mysql where you'd have to create the table to know about cascading deletes. Enjoy, Have FUN! H.Merijn	[reply] [d/l] [select]