1. Select the rows not  to be deleted into an empty table that has the same structure as the original table: 

INSERT INTO t_copy SELECT * FROM t WHERE ... ; 

2. Use RENAME TABLE  to atomically move the original table out of the way and rename the copy to the original name: 

RENAME TABLE t TO t_old, t_copy TO t; 

3. Drop the original table: 

DROP TABLE t_old; 

No other sessions can access the tables involved while RENAME TABLE  executes, so the rename operation is not subject to concurrency problems. See Section 13.1.36, “RENAME TABLE Statement” . 

## MyISAM Tables 

In MyISAM  tables, deleted rows are maintained in a linked list and subsequent INSERT  operations reuse old row positions. To reclaim unused space and reduce file sizes, use the OPTIMIZE TABLE  statement or the myisamchk  utility to reorganize tables. OPTIMIZE TABLE  is easier to use, but myisamchk  is faster. See Section 13.7.3.4, “OPTIMIZE TABLE Statement” , and Section 4.6.4, “myisamchk — MyISAM Table- Maintenance Utility” . 

The QUICK  modifier affects whether index leaves are merged for delete operations. DELETE QUICK  is most useful for applications where index values for deleted rows are replaced by similar index values from rows inserted later. In this case, the holes left by deleted values are reused. 

DELETE QUICK  is not useful when deleted values lead to underfilled index blocks spanning a range of index values for which new inserts occur again. In this case, use of QUICK  can lead to wasted space in the index that remains unreclaimed. Here is an example of such a scenario: 

1. Create a table that contains an indexed AUTO_INCREMENT  column. 

2. Insert many rows into the table. Each insert results in an index value that is added to the high end of the index. 

3. Delete a block of rows at the low end of the column range using DELETE QUICK . 

In this scenario, the index blocks associated with the deleted index values become underfilled but are not merged with other index blocks due to the use of QUICK . They remain underfilled when new inserts occur, because new rows do not have index values in the deleted range. Furthermore, they remain underfilled even if you later use DELETE  without QUICK , unless some of the deleted index values happen to lie in index blocks within or adjacent to the underfilled blocks. To reclaim unused index space under these circumstances, use OPTIMIZE TABLE . 

If you are going to delete many rows from a table, it might be faster to use DELETE QUICK  followed by OPTIMIZE TABLE . This rebuilds the index rather than performing many index block merge operations. 

## Multi-Table Deletes 

You can specify multiple tables in a DELETE  statement to delete rows from one or more tables depending on the condition in the WHERE  clause. You cannot use ORDER BY  or LIMIT  in a multiple-table DELETE . The table_references  clause lists the tables involved in the join, as described in Section 13.2.10.2, “JOIN Clause” . 

For the first multiple-table syntax, only matching rows from the tables listed before the FROM  clause are deleted. For the second multiple-table syntax, only matching rows from the tables listed in the FROM  clause 

