Home / How-To & Tutorials / Linux / How to quickly and efficiently delete all data in a LARGE MySQL table using TRUNCATE

How to quickly and efficiently delete all data in a LARGE MySQL table using TRUNCATE

If you have a very large table in MySQL and you need to delete all the data from it. Instead of using the DELETE syntax or DELETE FROM tablename; It is best to use the TRUNCATE syntax.

This will delete all data in the table very quickly. In MySQL the table is actually dropped and recreated, hence the speed of the query.

mysql> TRUNCATE TABLE tablename;

Query OK, 0 rows affected (10.34 sec)
  • Note: The number of deleted rows for MyISAM tables returned is zero; for INNODB it returns the actual number deleted.
By Kyle

About kyle

One comment

  1. Note that TRUNCATE only works if you have the DROP privilege, and any delete-based triggers don’t fire. So while it is more efficient, if you use triggers to maintain any sort of related state based on deletes from your table, TRUNCATE is a non-starter.

    BTW, it’s not true that TRUNCATE returns the number of rows deleted from an InnoDB table.

    CREATE TABLE `foo` (
    `id` int(11) NOT NULL,
    `shard_id` int(11) NOT NULL,
    `state` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `state` (`state`,`shard_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
    1 row in set (0.00 sec)

    (ravyn@localhost) [test]> select * from foo;
    +—-+———-+——-+
    | id | shard_id | state |
    +—-+———-+——-+
    | 1 | 1 | arf |
    | 2 | 1 | arf |
    | 3 | 1 | arf |
    +—-+———-+——-+
    3 rows in set (0.00 sec)

    (ravyn@localhost) [test]> truncate foo;
    Query OK, 0 rows affected (0.01 sec)

    Example from MySQL 5.5.32.

Leave a Reply

Your email address will not be published. Required fields are marked *

*