MySQL deletes some of the data from the oversized table

MySQL normal delete table

Definition of DELETE statement:

Children who often work with databases, delete the data most of the time with the DELETE statement. Now let's take a look at the definition of the DELETE statement.

DELETE [low_priority] [QUICK] [IGNORE] from Tbl_name

[WHERE Where_definition]

[Order BY ...]

[LIMIT Row_count]

Cases

Delete from friends where user_name = ' simaopig ';

Truncate statement truncate [TABLE] Tbl_name

Here is a simple example, I want to delete all the records in the Friends table, 111cn.net can use the following statement:

TRUNCATE TABLE friends;

But when I delete the oversized table, I find that delete is not, and I don't want to add the index. MySQL on the delete plus Low_priorty,quick,ignore estimate also does not help

There is a solution to seeing a MySQL document: http://dev.mysql.com/doc/refman/5.0/en/delete.html

If you are are deleting many rows from a large table, you could www.111cn.net exceed the lock table size for a InnoDB table. To avoid this problem, or simply to minimize the "time" this table remains locked, the following strategy (which does no t use deletes at all) might is helpful:

Select the rows is deleted into a empty table that has the same structure as the original table:

Insert into T_copy Select * from T Where ...;
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;
Drop The original table:

Drop TABLE T_old;

E text is not good, simple translation:

When you delete multiple rows of data on a compliance, the InnoDB exceeds the lock table size limit, and minimizing the time to lock the tables is:

1 Select data that does not need to be deleted and present them in an empty table with the same structure

2 Rename the original table and name the new table as the original table name of the original table

3 Erase Original Table

To summarize, you can use the new table, copy the data, delete the old table, and rename the data when you delete a portion of the large table.