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.
SELECT COUNT(*)
. Add proper unique index intosubscribers_update
and useINSERT IGNORE
. Additionally - add auto-initiated DATETIME/TIMESTAMP field intosubscribers
, and you may remove INSERT/UPDATE triggers at all.SHOW CREATE TABLE
; it is more descriptive thanDESCRIBE
. In particular, it shows the individual indexes, not "mul".