DELETE [LOW_PRIORITY] FROM tbl_name
[WHERE where_definition] [LIMIT rows]
DELETE deletes rows from
tbl_name that satisfy the condition
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,
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
operations reuse old record positions. To reclaim unused space and reduce
file sizes, use the
OPTIMIZE TABLE statement or the
utility to reorganize tables.
OPTIMIZE TABLE is easier, but
isamchk is faster.
OPTIMIZE TABLE, and
13.4.3 Table optimization.
LIMIT rows option to
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
DELETE command until the number of affected rows is less than