Delete Rows, Just before Drop table

Question

I heard like deleting all rows before dropping table will improve performance. I looked into Web, but couldn't find any reliable answers. Please share whether this is true and the reason behind. Also is the overall performance i.e, ( delete all rows + drop empty table ) is better than dropping table with all rows ?

@E G Nidheep don't know where you heard it (personally, I have never heard it before) but maybe you confuse it with the use of TRUNCATE which has better performance on deleting all rows of a table than DELETE as it does not count how many rows are deleted (whereas DELETE does). So if you want to empty a table, use the following for better performance TRUNCATE <table>, you can also check mysqltutorial.org/mysql-delete-statement.aspx — dZ.
– dZ., Commented Nov 5, 2019 at 13:08
he has a bit of a point.. @dZ. a DELETE can be rolled back in InnoDB a DROP table cant so there is less overhead for WAL or better said no WAL is used with DROP as in MySQL DROP is simply more or less directly mapped to the OS delete dir or file functions.. (comment is oversimplified) .. But it is wierd why you would delete all records before dropping the table.. — Raymond Nijland
– Raymond Nijland, Commented Nov 5, 2019 at 13:14
@RaymondNijland thanks for the clarification, didn't know about it, my answer was only for MySql (due to his tag) where we usually just drop the table, I have never heard or seen anything different. But thanks for the useful notice! :) — dZ.
– dZ., Commented Nov 5, 2019 at 13:51
"my answer was only for MySql (due to his tag) where we usually just drop the table, I" i know @dZ. mine comment is also based for MySQL, well MariaDB should also work.. Also the reason why i placed "But it is wierd why you would delete all records before dropping the table" in mine comment ... Anyhow it is wierd to do anyway in any RDMS to delete all records before dropping the table well some RDMS support rollbacking a DROP aswell but not totally sure how it is implemented there it might work internally might be as simple as a dir or file rename on disk. .. — Raymond Nijland
– Raymond Nijland, Commented Nov 5, 2019 at 14:20
@dZ. I know that TRUNCATE is better than DELETE (if u want to empty the table), thats where all the doubt came, TRUNCATE is like drop the table and CREATE the table again, which is faster than DELETE, then how DELETING all rows before before DROP, will improve DROPS performance. — E G Nidheep
– E G Nidheep, Commented Nov 6, 2019 at 4:39

E G Nidheep · Accepted Answer · 2019-11-06 12:58:59Z

1

Deleting all rows before Drop is additional expense. Dropping with and without rows doesn't have much difference. ( tried with 1M rows )

answered Nov 6, 2019 at 12:58

E G Nidheep

112 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Delete Rows, Just before Drop table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related