Avoiding deadlocks when using trigger

Question

I have a database with a table called subscribers. The engine is InnoDB. The table has approximately 100,000 rows. This table contains multiple columns:

 CREATE TABLE `subscribers` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `userID` int(11) DEFAULT NULL,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `custom_fields` longtext COLLATE utf8mb4_unicode_ci,
  `list` int(11) DEFAULT NULL,
  `unsubscribed` int(11) DEFAULT '0',
  `bounced` int(11) DEFAULT '0',
  `bounce_soft` int(11) DEFAULT '0',
  `complaint` int(11) DEFAULT '0',
  `last_campaign` int(11) DEFAULT NULL,
  `last_ares` int(11) DEFAULT NULL,
  `timestamp` int(100) DEFAULT NULL,
  `join_date` int(100) DEFAULT NULL,
  `confirmed` int(11) DEFAULT '1',
  `messageID` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ip` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `country` varchar(2) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `referrer` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `method` int(1) DEFAULT NULL,
  `added_via` int(1) DEFAULT NULL,
  `gdpr` int(1) DEFAULT '0',
  `notes` text COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `s_list` (`list`),
  KEY `s_unsubscribed` (`unsubscribed`),
  KEY `s_bounced` (`bounced`),
  KEY `s_bounce_soft` (`bounce_soft`),
  KEY `s_complaint` (`complaint`),
  KEY `s_confirmed` (`confirmed`),
  KEY `s_timestamp` (`timestamp`),
  KEY `s_email` (`email`),
  KEY `s_last_campaign` (`last_campaign`),
  KEY `s_messageid` (`messageID`),
  KEY `s_country` (`country`),
  KEY `s_referrer` (`referrer`),
  KEY `s_method` (`method`),
  KEY `s_added_via` (`added_via`),
  KEY `s_gdpr` (`gdpr`)
) ENGINE=InnoDB AUTO_INCREMENT=446314 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

These columns track the email of the subscriber, the subscription status, etc. I want to keep track of any changes to this table to keep another database, in a different server, and with a different structure, up to date with the changes.

So I created another table, subscribers_update.

CREATE TABLE IF NOT EXISTS subscribers_update (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `subscriber_id` int(11) DEFAULT NULL,
    `email` varchar(100) DEFAULT NULL,
    `list` int(11) DEFAULT NULL,
    `action` varchar(50) DEFAULT NULL,
    PRIMARY KEY (`id`)
) AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

and then setup 3 different triggers for UPDATE, INSERT, and DELETE, respectively.

# when user updates his or her information
DELIMITER $$

CREATE TRIGGER after_subscribers_update
    AFTER UPDATE ON subscribers
    FOR EACH ROW
BEGIN
    IF (SELECT COUNT(*) FROM subscribers_update
            WHERE subscriber_id = NEW.id
              AND email = NEW.email
              AND list = NEW.list
              AND action = 'update') = 0
    THEN
        INSERT INTO subscribers_update
        SET subscriber_id = NEW.id,
            email = NEW.email,
            list = NEW.list,
            action = 'update';
    END IF;
END;

$$
DELIMITER ;

# when user is added through a sign-up form
DELIMITER $$

CREATE TRIGGER after_subscribers_insert
    AFTER INSERT ON subscribers
    FOR EACH ROW
BEGIN
    IF (SELECT COUNT(*) FROM subscribers_update WHERE subscriber_id = NEW.id AND email = NEW.email AND list = NEW.list
               AND action = 'insert') = 0
    THEN
        INSERT INTO subscribers_update
        SET subscriber_id = NEW.id,
            email = NEW.email,
            list = NEW.list,
            action = 'insert';
    END IF;
END;

$$
DELIMITER ;

# when user is deleted
DELIMITER $$

CREATE TRIGGER after_subscribers_delete
    AFTER DELETE ON subscribers
    FOR EACH ROW
BEGIN
    IF (SELECT COUNT(*) FROM subscribers_update WHERE subscriber_id = OLD.id AND email = OLD.email AND list = OLD.list
             AND action = 'delete') = 0
    THEN
        INSERT INTO subscribers_update
        SET subscriber_id = OLD.id,
            email = OLD.email,
            list = OLD.list,
            action = 'delete';
    END IF;
END;

$$
DELIMITER ;

Finally, I have a cron job that regularly checks subscribers_update for any changes, and runs a script to update the second remote database. Again, this second database has a completely different structure.

Upon closer inspection, it seems these triggers drastically slow down the server, and cause multiple deadlocks. A "simple" query such as UPDATE subscribers SET bounce_soft = 0 WHERE list IN (15); pretty much kills the server for a few minutes, and causes deadlocks according to the results from show engine innodb status.

I'm far for a database expert, so I would like to know how to optimize my triggers, and/or if there is a better way to keep track of changes to this table to update the second database.

Answer 1

IF (SELECT COUNT(*) FROM subscribers_update
        WHERE subscriber_id = NEW.id
          AND email = NEW.email
          AND list = NEW.list
          AND action = 'update') = 0

-->

IF ( NOT EXISTS( SELECT 1 FROM subscribers_update
        WHERE subscriber_id = NEW.id
          AND email = NEW.email
          AND list = NEW.list
          AND action = 'update') )

and add a composite index:

INDEX(subscriber_id, email, list, action)  -- in any order