2

I have an all-InnoDB database in MySQL (Percona) 5.6 that was previously using FK constraints. I had to give up on them as I kept running into locking issues, so I removed the constraints and added some manual cleanup maintenance scripts instead. These queries are causing problems as they appear to be very inefficient, so I'm wondering how they can be made to work more efficiently.

A typical arrangement is this:

t1

id, field1

t2

id, t1_id, field1

t1_id in t2 is a foreign key pointing at the id field of t1. Each table has id set as its primary key, and t2 has a regular index on t1_id.

So, instead of a foreign key constraint dealing with cascade deleting records in t2 when a parent record in t1 is deleted, I have a cleanup query like this:

DELETE t2.* FROM t2 LEFT JOIN t1 ON t1.id = t2.t1_id WHERE t2.t1_id IS NOT NULL AND t1.id IS NULL;

in other words, delete any record in t2 that points at a non-existent record in t1.

That works, but it's really slow. Here's the explain for it:

+----+-------------+-------+--------+---------------+---------+---------+---------------+---------+----------+--------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows    | filtered | Extra                                |
+----+-------------+-------+--------+---------------+---------+---------+---------------+---------+----------+--------------------------------------+
|  1 | SIMPLE      | t2    | range  | t1_id         | t1_id   | 5       | NULL          | 2392097 |   100.00 | Using where                          |
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY       | PRIMARY | 4       | mydb.t2.t1_id |       1 |   100.00 | Using where; Not exists; Using index |
+----+-------------+-------+--------+---------------+---------+---------+---------------+---------+----------+--------------------------------------+

The obvious problem here is that the row count is very high, and other questions I've seen on this subject suggest the range match type is at the root of the problem. If I omit the check for NOT NULL on t1_id, it matches the whole of the t2 table, making the row count even higher.

Because this is a single query, it tends to lock everything in t2 until it's complete, holding up other queries. I tried replacing it with separate select and delete, but it doesn't help much.

How can this be done better?

closed as off-topic by Paul White, Max Vernon, RLF, Mark Storey-Smith, Philᵀᴹ Jan 16 '15 at 17:44

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Tip of the iceberg - the question or comments reveal an underlying issue that would need extensive investigation by a consultant or database vendor support team: issues like this do not fit the SE Q&A model well. For more information see this meta post." – Paul White, Max Vernon, RLF, Mark Storey-Smith, Philᵀᴹ
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Find out how many what the min/ max values for table column t2.id is and loop through for a smaller set of records. For example if you have 100,000 records, then create a loop that looks at ranges of maybe 2000 at a time. – BateTech Jan 8 '15 at 13:26
  • I would be careful blindly deleting foreign keys. You might be better off to index the foreign key columns in the referencing tables. – BateTech Jan 8 '15 at 13:29
  • Did/Do you have indexes on the tables? I'd never consider dropping FK constraints and implementing the behaviour myself. I suggest you investigate better where and why the locking issues arise. – ypercubeᵀᴹ Jan 8 '15 at 13:34
  • And why do you need an always running cleanup query anyway? Why not delete the related rows of t2 whenever you delete a row in t1 (and in the same transaction to ensure a consistent state of your database)? – ypercubeᵀᴹ Jan 8 '15 at 14:04
  • 2
    Deleting a row (or a few rows) takes milliseconds. Why would your app need minutes for that? – ypercubeᵀᴹ Jan 8 '15 at 14:27
1

Try batching the deletes, so that you are only checking a small set of records at a time, and so are locking a smaller set of records. You can play around with increase or decreasing the size of @batchSize until you find the "magic number" that performs best for you. Note: I don't normally work in MySQL so my while loop syntax may not be 100% correct, but the general idea is there.

DECLARE @i int;
DECLARE @batchSize int;
DECLARE @maxKey int;

SET @i = 0;
SET @batchSize = 2000;
SET @maxKey = (select max(id) from t2 where t2.tl_id IS NOT NULL);


WHILE (@i < @maxKey) DO
BEGIN
    DELETE t2
    FROM t2
    LEFT JOIN t1 ON t2.t1_id = t1.id
    WHERE t2.id >= @i and t2.id <= @i + @batchSize
        AND t2.t1_id is not null
        AND t1.ID is null
    ;

    SET @i = @i + @batchSize;
END WHILE

Not the answer you're looking for? Browse other questions tagged or ask your own question.