Mysql delete operation and mysqldelete operation

Mysql delete operation and mysqldelete operation

The following is an excerpt from the official document:

Syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROMtbl_name[PARTITION (partition_name,...)] [WHEREwhere_condition] [Order by...] [LIMITrow_count]


Performance:
When you do not need to know the number of deleted rows,TRUNCATE TABLEStatement is a faster way to empty a table thanDELETEStatement with noWHEREClause. UnlikeDELETE,TRUNCATE TABLECannot be used within a transaction or if you have a lock on the table. SeeSection 14.1.34, "truncate table Syntax" and Section 14.3.5, "lock tables and unlock tables Syntax ".
To put it simply, truncate is very fast without locking the table:

If you want to delete data using delete, hurry up:
The time required to delete individual rows inMyISAMTable is exactly proportional to the number of indexes. To delete rows more quickly, you can increase the size of the key cache by increasingkey_buffer_sizeSystem variable
Configurable key_buffer_size
Delete multiple tables:
(1) without aliases

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3WHERE t1.id=t2.id AND t2.id=t3.id;

Or:

Delete from t1, t2 USING t1 inner join t2 inner join t3WHERE t1.id = t2.id AND t2.id = t3.id;
(2) aliases: aliases must be written:

If you declare an alias for a table, you must use the alias when referring to the table:

DELETE t1 FROM test AS t1, test2 WHERE ...

Correct:

DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2WHERE a1.id=a2.id;DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2WHERE a1.id=a2.id;