How to archive record with records from other connected tables?

Question

I've got 8 tables:

users:
  uid
users_removed:
  uid
messages:
  mid
  uid FK users (uid)
messages_removed:
  mid
  uid
comments:
  cid
  mid FK messages (mid)
comments_removed:
  cid
  mid
files:
  fid
  mid FK messages (mid)
files_removed:
  fid
  mid

When I remove record from table "users" I want move it to table users_removed (before deleting from users). What I also want is to move all corresponding messages (and files and comments) to *_removed tables.

I've used triggers:

CREATE TRIGGER delete_user BEFORE DELETE ON users
    FOR EACH ROW BEGIN
        INSERT IGNORE INTO users_removed
            SELECT * FROM users WHERE uid = OLD.uid;
        DELETE FROM messages WHERE OLD.uid in (owner_id, author_id);
    END
|

CREATE TRIGGER delete_message BEFORE DELETE ON messages
    FOR EACH ROW BEGIN
        INSERT IGNORE INTO messages_removed
            SELECT * FROM messages WHERE mid = OLD.mid;
        DELETE FROM comments WHERE mid = OLD.mid;
        DELETE FROM files WHERE mid = OLD.mid;
    END
|

CREATE TRIGGER delete_comment BEFORE DELETE ON comments
    FOR EACH ROW BEGIN
        INSERT IGNORE INTO comments_removed
            SELECT * FROM comments WHERE cid = OLD.cid;
    END
|

CREATE TRIGGER delete_file BEFORE DELETE ON files
    FOR EACH ROW BEGIN
        INSERT IGNORE INTO files_removed
            SELECT * FROM files WHERE fid = OLD.fid;
    END
|

But it works very slow with > 50k users, > 1m messages, comments and files.

Is there any fast method to do that?

Answer 1

1. There's no fast way to do this. Assuming you're using InnoDB, you have to write a table entry, remove a table entry reference, and then InnoDB does a second cleanup run to actually get rid of it (deleting is expensive). Then, having those holes in your database will almost certainly eventually hurt performance as you delete more users over time.

2. If you're saving the user info, why not just add a column to the users table called "active" with enum (true, false)?