Description:
This is a request to improve TRUNCATE TABLE for InnoDB tables with foreign keys.
Currently, the documentation says this:
"For an InnoDB table, InnoDB processes TRUNCATE TABLE by deleting rows one by one if there are any FOREIGN KEY constraints that reference the table. If there are no FOREIGN KEY constraints, InnoDB performs fast truncation by dropping the original table and creating an empty one with the same definition, which is much faster than deleting rows one by one."
http://dev.mysql.com/doc/refman/5.1/en/truncate.html
Obviously, deleting rows one-by-one is quite slow, and so there are some cases where this could be avoided, even when foreign keys exist.
When would this be possible?
Well, consider the following setup (3 tables):
TABLE TABLE_A ..... column_1 references TABLE_B ( column_1 )
TABLE TABLE_B ( column_1 <definition> ...., column_n references TABLE_C (column_1) )
TABLE TABLE_C ( column_1 <definition> .... )
It's NOT ok to TRUNCATE TABLE TABLE_B unless ALL tables which reference TABLE_B are also empty (in this case TABLE_A), otherwise you'll break a FK constraint.
It's NOT ok to TRUNCATE TABLE TABLE_C unless ALL tables which reference TABLE_C are also empty (in this case TABLE_B), otherwise you'll break a FK constraint.
So it's necessary in this case to TRUNCATE in the following order:
TRUNCATE TABLE TABLE_A;
TRUNCATE TABLE TABLE_B;
TRUNCATE TABLE TABLE_C;
Assuming you TRUNCATED in this order (or in other such similar situations), there is no need to perform the TRUNCATE in a row-by-row manner. We know the referencing tables are empty, so we should not have to check each row one-by-one. This is inefficient and seems unnecessary.
In cases like this, where the referencing/child table is empty, there is no need to perform this row-by-row check.
Further, if the foreign keys are circular, that is defined on itself, then there should also be no need to perform the row-by-row checking. Just DROP and re-CREATE the table.
Lastly, and this differs from the above slightly, but still applies to improving TRUNCATE TABLE for InnoDB. It would be nice if when TRUNCATE TABLE is run against an InnoDB table where innodb_file_per_table is enabled, then the TRUNCATE should simply reclaim the space in the individual .ibd file.
How to repeat:
N/A.
Suggested fix:
N/A.
