MariaDB how to break down on delete cascade

Question

I have a MariaDB database version 10.3.38 and a table called mw_list that has a column that is referenced by other tables (ex mw_fields, mw_subscribers). Other tables may also have constraints referencing mw_fields or mw_subscribers so you know where this is going. If i delete a row from mw_list (the parent table) a chain reaction will occur and that means that constraints should enforce deletes on child tables. The problem is that I get timeout because the tables are huge.

Question 1: when deleting a parent row from mw_list, does the query go, let's say on pause until a subquery for the constraint gets completed?

Question 2: when I get timeout, I go check the child tables and there aren't any rows missing. Like the delete didn't occur. Why could this happen?

Question 3: how can I break down this process? By removing the constraints and rebuilding new queries with join clauses?

I tried deleting one or two rows but I got timeout. I was expecting the query to complete or at least delete a portion of the child rows.

Answer 1

Data manipulation statements (like your cascading DELETE) are transactional. The whole statement finishes, or the whole statement is rolled back to the starting state (as in a timeout). That explains your observation that none of the child rows were deleted.

How to fix this? Here are some choices.

1. Track down all the child tables, and delete child rows from those tables explicitly before deleting the row from the parent table. It will take a bit more SQL, but won't be as likely to time out.

2. Another possibility. Increase the timeout using max_statement_time something like this. This will give your query ten minutes (600 sec) to run.

   SET STATEMENT max_statement_time=600 FOR 
     DELETE FROM mw_list WHERE whatever
   

3. You didn't show us your table definitions so this is a guess: change your tables' ON DELETE CASCADE statements to ON DELETE RESTRICT. Then you won't be able to delete any parent rows while child rows are still in place. Then follow step 1.

4. Disable foreign key checks, and delete child and parent rows explicitly. But be careful: defects in your programming can lead to orphan rows in child tables.

   SET @@session.foreign_key_checks=0;
   DELETE FROM ....
   

5. Don't use foreign keys at all. Your database will be faster. Read on...

Pro tip The purpose of explicit foreign key definitions is to enforce integrity rules, one of which is "no orphan rows". If your app is resilient to a few orphan rows and other referential integrity problems you can operate it, in production, without foreign key definitions. For big tables this makes INSERT, UPDATE, and DELETE operations substantially faster. Many production apps don't use FKs, for precisely the performance reason you mentioned. You'll have to update your code to delete child rows before deleting the parent row. Even with the best coding you will, in a live system, occasionally get orphans, and you'll need a process to clean them up. I've used a weekly job for that purpose. It deletes orphans and sends email to the responsible person when it finds any to delete.