Can I disable the redo log for a DELETE query or a whole transaction?

Question

I'm working on an existing database, whose structure I can't change. I need to clear (empty) several InnoDB tables (that I can't TRUNCATE due to references in other tables' FK constraints), but I have no interest in any recovery - i.e. if a crash occurs during those operations, I'll clear those tables again on the first occasion.

Can I tell MySQL to not create the redo log about those queries?

I am concerned about speed, as the first thing that can be noted is the time the operation takes. But, space too, I guess. Tables can store 20k-1M rows depending on the specific installation (same schema structure, different usages). I am operating on a bunch of table whose data are related (i.e. products, products_images, products_votes, ...), so FK are not an issue because all data will be deleted consistently without the need of any additional check.

Answer 1

If I understand correctly, the problem is:

We have one or more tables (lets call them "parents") that we want to empty, either with DELETE or TRUNCATE. There are several other tables ("children") that have FOREING KEY constraints which reference the first table(s) or some other "child" and we want to empty those tables as well.

• If we DELETE, the operation will succeed, as all the FKs have been defined with ON DELETE CASCADE actions. But it will be a very slow operation.

• If we TRUNCATE, the operation fails due to the FKs. It's not allowed to TRUNCATE a table that other tables reference it.

To solve this conundrum, I don't think that messing with the redo log is advisable (if at all possible).

I suggest the more simple:

• ALTER the children tables and DROP the offending FOREIGN KEY constraints.

• TRUNCATE all the tables.

• ALTER the children tables and add the constraints again.

If some reason altering the tables even temporarily is forbidden, you should at least follow MySQL's guidelines about optimizing the redo logs: Optimizing InnoDB Redo Logging