7.10 DELETE syntax

DELETE [LOW_PRIORITY] FROM tbl_name
    [WHERE where_definition] [LIMIT rows]

DELETE deletes rows from tbl_name that satisfy the condition given by where_definition, and returns the number of records deleted.

If you issue a DELETE with no WHERE clause, all rows are deleted. MySQL does this by recreating the table as an empty table, which is much faster than deleting each row. In this case, DELETE returns zero as the number of affected records. (MySQL can't return the number of rows that were actually deleted, since the recreate is done without opening the data files. As long as the table definition file `tbl_name.frm' is valid, the table can be recreated this way, even if the data or index files have become corrupted.).

If you really want to know how many records are deleted when you are deleting all rows, and are willing to suffer a speed penalty, you can use a DELETE statement of this form:

mysql> DELETE FROM tbl_name WHERE 1>0;

Note that this is MUCH slower than DELETE FROM tbl_name with no WHERE clause, because it deletes rows one at a time.

If you specify the keyword LOW_PRIORITY, execution of the DELETE is delayed until no other clients are reading from the table.

Deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. To reclaim unused space and reduce file sizes, use the OPTIMIZE TABLE statement or the isamchk utility to reorganize tables. OPTIMIZE TABLE is easier, but isamchk is faster. OPTIMIZE TABLE, and 13.4.3 Table optimization.

The MySQL-specific LIMIT rows option to DELETE tells the server the maximum number of rows to be deleted before control is returned to the client. This can be used to ensure that a specific DELETE command doesn't take too much time. You can simply repeat the DELETE command until the number of affected rows is less than the LIMIT value.