[MySQL]How to drop a large table

We can do drop table but it takes long time

mysql> DROP table erp;

We should enable innodb file per table in my.cnf first

innodb_file_per_table = 1

The reason that make delete table slow is because we have large .ibd file

ls -l /data/mysql/mytest/
-rw-r----- 1 mysql mysql          9023  8 18 05:21 erp.frm
-rw-r----- 1 mysql mysql 2356792000512  8 18 05:21 erp.ibd

1.Create a hard link to ibd file

ln /data/mysql/mytest/erp.ibd /data/mysql/mytest/erp.ibd.hdlk 
ls -l /data/mysql/mytest/
-rw-r----- 1 mysql mysql          9023  8 18 05:21 erp.frm
-rw-r----- 2 mysql mysql 2356792000512  8 18 05:21 erp.ibd
-rw-r----- 2 mysql mysql 2356792000512  8 18 05:21 erp.ibd.hdlk 

2.Now we can drop table

mysql> drop table erp;
Query OK, 0 rows affected (0.99 sec)

3.Remove the real file erp.ibd.hdlk on OS
We could directly do rm on it but it cost high disk I/O
alternatively, we could use TRUNCATE

#!/bin/bash
TRUNCATE=/usr/local/bin/truncate
for i in `seq 2194 -10 10 `; 
do 
  sleep 2
  $TRUNCATE -s ${i}G /data/mysql/mytest/erp.ibd.hdlk 
done
rm -rf /data/mysql/mytest/erp.ibd.hdlk ;

reference

Subscribe
Notify of

520 Comments
Inline Feedbacks
View all comments

cialis 5mg prix http://andere.strikingly.com/

This is nicely said. .

cialis uk https://kertvbs.webgarden.com/

Incredible loads of valuable material.

tadalafil tablets https://gwertvb.mystrikingly.com/

Whoa plenty of valuable knowledge!

cialis from canada https://telegra.ph/Is-It-Safe-To-Lift-COVID-19-Travel-Bans-04-06

Appreciate it. Quite a lot of material.

cialis canadian pharmacy https://canadian-pharmacies0.yolasite.com/

Thanks a lot! I like this.

tadalafil tablets https://pharmacy-online.yolasite.com/

With thanks. An abundance of forum posts.

buy generic cialis https://kevasw.webgarden.com/

You actually revealed it very well.

Canadian Pharmacy USA https://62553dced4718.site123.me/

Nicely put. Appreciate it.

prescriptions from canada without https://seketu.gonevis.com/high-10-tips-with-order-medicine-online-1/

This is nicely put! !

cialis tablets https://site128620615.fo.team/

Wonderful content, Cheers!

cialis tablets https://fwervs.gumroad.com/

You have made your point quite clearly.!

purchasing cialis on the internet https://trosorin.mystrikingly.com/

Really a good deal of superb material.

generic for cialis https://generic-cialis-20-mg.yolasite.com/

Kudos! I enjoy it!

tadalafil without a doctor’s prescription https://hemuyrt.livejournal.com/325.html

Nicely put, Kudos.

tadalafil https://site373681070.fo.team/

Amazing all kinds of great facts!

cialis without a doctor’s prescription https://sehytv.wordpress.com/

Thanks a lot. I appreciate this.

no 1 canadian pharcharmy online https://ghswed.wordpress.com/2022/04/27/he-final-word-information-to-online-pharmacies/

Seriously many of wonderful advice.

top rated online canadian pharmacies https://kerbgsw.mystrikingly.com/

Reliable data. Appreciate it!

cialis 5 mg https://kerbiss.wordpress.com/2022/04/27/14/

Nicely put. Many thanks.

cialis without a doctor’s prescription https://heswcxc.wordpress.com/2022/04/30/online-medicine-tablets-shopping-promotion-one-hundred-and-one/

Incredible all kinds of helpful data!

tadalafil 10 mg https://kertubs.mystrikingly.com/

Nicely put, Thanks!

medication without a doctors prescription https://canadian-pharmaceuticals-online.yolasite.com/

Nicely put, With thanks.

cialis tablets https://online-pharmacies0.yolasite.com/

You suggested that effectively.

cialis prices https://deiun.flazio.com/

Wow all kinds of good knowledge.

canada viagra https://kertyun.flazio.com/

Very good facts. Appreciate it!

online prescriptions without a doctor https://kerbnt.flazio.com/

Kudos! Plenty of posts.

generic cialis http://nanos.jp/jmp?url=http://cialisonlinei.com/

Many thanks. I like this!

tadalafil generic http://ime.nu/cialisonlinei.com

Regards! I value this.

Canadian Pharmacy USA https://kerntyast.flazio.com/

With thanks, I enjoy this!