0

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.

  • Remove SELECT COUNT(*). Add proper unique index into subscribers_update and use INSERT IGNORE. Additionally - add auto-initiated DATETIME/TIMESTAMP field into subscribers, and you may remove INSERT/UPDATE triggers at all. – Akina Nov 1 '19 at 9:36
  • @Akina I can do that. Thank you! Would I still need a trigger for when a row is deleted? Because if a row is gone, I assume the DATETIME/TIMESTAMP would also be gone. And I would need to delete said row in the second database. – Hernan Villanueva Nov 1 '19 at 15:51
  • Would I still need a trigger for when a row is deleted? Yes. You have quite correctly identified the cause. – Akina Nov 1 '19 at 16:44
  • Ok, I'll try that. Thanks! – Hernan Villanueva Nov 1 '19 at 17:04
  • Please provide SHOW CREATE TABLE; it is more descriptive than DESCRIBE. In particular, it shows the individual indexes, not "mul". – Rick James Nov 2 '19 at 1:23
0
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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