[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