truncate table taking very long time, is it normal?

Question

I am using truncate table table_name; on a table with around 1 million rows, but it's been taking too long, running since last 3 hours.

Is it normal? Can you suggest some other way to delete all rows from a table, which could be faster?

i have killed the query, by the time i killed it already deleted 600 thousand records, but since data was not important to me so i just copied the structure of old table to a new one and then dropped the old one. — smrati katiyar, Jul 20 '13 at 13:10

Kalaiarasan Manimaran · Accepted Answer · 2015-07-07T13:30:58.053

62

Truncate wont work in some cases such as ,

when you have index kind of things and some foreign key constraints

Easy way i suggest is

RENAME TABLE table_name TO t1;

CREATE TABLE table_name LIKE t1;

DROP TABLE t1;

or you can also use DELETE FROM table_name;

edited Jul 07 '15 at 13:30

answered Jul 20 '13 at 12:57

Kalaiarasan Manimaran

1,598
1
12
18

1

This saved us a ton of time - truncate wasn't working because of foreign key references, but we needed to get rid of a logging table that was ~22GB in size (over 23M rows) and this made it so much faster. – Scott Salyer Sep 16 '21 at 17:22
This works for me like a charm! Thanks! To add something, I was also checking it through a UI client (DBeaver) and it happens sometimes that when you list the data through the table directly it does not get disconnected even if you close the tab – Darkaico Dec 30 '22 at 11:52

score 4 · Answer 2 · edited May 24 '18 at 08:57

4

I believe deadlock occurs during your query execution, so it's better to kill it .

I used to delete lots of data, by deleting small chunk in a single query (10k works fine).

So you might write some script which will do this for you.

edited May 24 '18 at 08:57

Superleggera

153
13

answered Jul 20 '13 at 12:54

Uriil

11,948
11
47
68

1

if truncate is slow i am thinking of creating a new table with same structure and then dropping the old one, i think that will be quite fast. pls share ur views on this. – smrati katiyar Jul 20 '13 at 12:58
the problem I had was that a stale connection was stuck doing a commit to the table so it was frozen. – fIwJlxSzApHEZIl Feb 25 '20 at 22:28

score 1 · Answer 3 · answered Dec 26 '18 at 13:53

Given issue is logged in mysql repo. For more details visit https://bugs.mysql.com/bug.php?id=68184

In my case, I was trying to trucate table using JDBC But due the above i was not able to do that. So was going through below alternative.

    String createTmpTable = "create table tmp_" + tableName + " like " + tableName;
    String dropTable = "drop table " + tableName;
    String reCreateTable = "create table " + tableName + " like tmp_" + tableName;
    String droptmpTable = "drop table tmp_" + tableName;

truncate table taking very long time, is it normal?

3 Answers3