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.