CODEWITHSUNDEEP
mysql
smrati katiyar
smrati katiyar

Reputation: 517

truncate table taking very long time, is it normal?

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?

Upvotes: 29

Views: 60217

Answers (3)

Balkrushna Patil
Balkrushna Patil

Reputation: 466

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;

Upvotes: 1

Uriil
Uriil

Reputation: 12628

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.

Upvotes: 4

Kalaiarasan Manimaran
Kalaiarasan Manimaran

Reputation: 1658

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;

Upvotes: 68

Related Questions