Search code examples
mysqlsqlsql-delete

Comparison of Drop , Delete or Truncate in MySQL on basis of query running speed

Which query is faster among these:

  1. DROP TABLE table_Name
  2. TRUNCATE TABLE table_Name
  3. DELETE FROM table_Name
Solution

  • In MySQL, for a table with a significant number of rows, I would suppose that drop is the fastest operation, then truncate, and finally delete.

    Rationale:

    • drop and truncate are DDL operations, as opposed to delete, which is a DML operation; as the number of rows increases, the performance of delete degrades quickly (while DDL operations are less dependent on the underlying dataset size).

    • in MySQL, truncate under the hood drops and recreates the table - so it cannot be faster than a straight drop