CODEWITHSUNDEEP
mysql
Vijay Kansal
Vijay Kansal

Reputation: 839

Drop Create table vs Truncate table in Mysql

In a daily cron job, I need to create a table in Mysql, do some processing and at the end drop it (no longer need this table).

My question is which of the below two strategies is better in terms of CPU utilization and memory footprint:

  1. CREATE table at the start of the job and DROP it at the end.
  2. TRUNCATE table at the end of the job (i.e. table is never dropped, just that it is truncated after every job run).

In the 1st approach, a new table has to be created in every job run, whereas in the 2nd approach, table remains in Mysql 24*7 even though it is not needed after the job run.

Upvotes: 0

Views: 269

Answers (2)

Emipro Technologies Pvt. Ltd.
Emipro Technologies Pvt. Ltd.

Reputation: 14746

At the beginning use this query

Select * into test from Table

And at the last truncate it and drop it.

Truncate table test
Or
Drop table test

Upvotes: 0

Omar Saad
Omar Saad

Reputation: 21

you can create temporary table instead of creating actual table:

CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (SELECT * FROM table1)

Upvotes: 2

Related Questions