Re: [SQL] Efficient DELETE Strategies - Mailing list pgsql-hackers

> Given the plethora of mutually incompatible interpretations that MSSQL
> evidently supports, though, I fear we can't use it as precedent for
> making any choices :-(.
>
> Can anyone check out other systems?

MySQL:

6.4.6 DELETE Syntax

DELETE [LOW_PRIORITY | QUICK] FROM table_name      [WHERE where_definition]      [ORDER BY ...]      [LIMIT rows]

or

DELETE [LOW_PRIORITY | QUICK] table_name[.*] [,table_name[.*] ...]      FROM table-references      [WHERE
where_definition]

or

DELETE [LOW_PRIORITY | QUICK]      FROM table_name[.*], [table_name[.*] ...]      USING table-references      [WHERE
where_definition]

DELETE deletes rows from table_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. If you do
this in AUTOCOMMIT mode, this works as TRUNCATE. See section 6.4.7 TRUNCATE
Syntax. In MySQL 3.23, DELETE without a WHERE clause will return zero as the
number of affected records.

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 table_name WHERE 1>0;

Note that this is much slower than DELETE FROM table_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.

If you specify the word QUICK then the table handler will not merge index
leaves during delete, which may speed up certain kind of deletes.

In MyISAM tables, 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
myisamchk utility to reorganise tables. OPTIMIZE TABLE is easier, but
myisamchk is faster. See section 4.5.1 OPTIMIZE TABLE Syntax and section
4.4.6.10 Table Optimisation.

The first multi-table delete format is supported starting from MySQL 4.0.0.
The second multi-table delete format is supported starting from MySQL 4.0.2.

The idea is that only matching rows from the tables listed before the FROM
or before the USING clause are deleted. The effect is that you can delete
rows from many tables at the same time and also have additional tables that
are used for searching.

The .* after the table names is there just to be compatible with Access:

DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id

or

DELETE FROM t1,t2 USING t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id

In the above case we delete matching rows just from tables t1 and t2.

ORDER BY and using multiple tables in the DELETE statement is supported in
MySQL 4.0.

If an ORDER BY clause is used, the rows will be deleted in that order. This
is really only useful in conjunction with LIMIT. For example:

DELETE FROM somelog
WHERE user = 'jcole'
ORDER BY timestamp
LIMIT 1

This will delete the oldest entry (by timestamp) where the row matches the
WHERE clause.

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.

Chris