Empty MySQL table rapidly

8 04 2010

It is not fun if we need to clear a huge table in MySQL. If we just delete all rows or truncate the table, it will take a very long time to finish. Moreover, spaces in used are not reclaimed immediately, you need to run “optimize” which takes a lot longer time to complete. I had an experience on this situation after waiting for 4 days, it still optimised my table. That’s annoying. So, I introduced a simpler method to clean MySQL table.

0. Before cleaning our table, let’s see the total consumed space
$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/cciss/c0d0p1 529G 405G 98G 81% /
mysql> show tables;
+------------------+
| Tables_in_syslog |
+------------------+
| actions |
| cemdb |
| logs |
| search_cache |
| user_access |
| users |
+------------------+
6 rows in set (0.00 sec)
mysql> SHOW columns in logs;
... The result is left

1. Copy the structure of the table we want to empty, not data
mysql> CREATE TABLE logs2 LIKE logs;
Query OK, 0 rows affected (1.22 sec)

2. Drop the target table
mysql> DROP TABLE logs;
Query OK, 0 rows affected (6 min 30.45 sec)

3. Resurrect the old table
mysql> CREATE TABLE logs LIKE logs2;
Query OK, 0 rows affected (0.00 sec)

4. Now, remove our temporary table
mysql> DROP TABLE logs2;
Query OK, 0 rows affected (0.00 sec)

5. Find the available space we have
$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/cciss/c0d0p1 529G 102G 401G 21% /

That’s it. Easier and quicker method are all we claim with this technique.


Actions

Information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




%d bloggers like this: