Mysql – Why are parallel writes in MySQL as slow as sequential writes

You may need to create the counter table with 250 slots to spread the row locks.

First, here is the table:

CREATE TABLE HourCounter
(
    hr INT,
    slot TINYINT,
    cnt INT UNSIGNED,
    PRIMARY KEY (hr,slot)
) ENGINE=InnoDB;

All you need to do is insert into the random slots like this:

SET @NumberOfSlots = 250;
SET @X = UNIX_TIMESTAMP();
SET @uthr = @X - MOD(@X,3600);
INSERT INTO HourCounter (hr,slot,cnt)
    VALUES (@uthr,RAND() * @NumberOfSlots + 1,1)
    ON DUPLICATE KEY UPDATE cnt = cnt + 1
;

If you want to collect the counts, run this:

SELECT hr,SUM(cnt) HourCount
FROM HourCounter GROUP BY hr;

If you want to collapse the slots into a single number, do this once a week:

UPDATE HourCounter c
    INNER JOIN
    (
        SELECT hr,SUM(cnt) cnt,MIN(slot) mslot
        FROM HourCounter GROUP BY hr
    ) x USING (hr)
SET c.cnt  = IF(c.slot = x.mslot, x.cnt, 0),
    c.slot = IF(c.slot = x.mslot, 0, c.slot);
DELETE FROM HourCounter WHERE slot <> 0 AND cnt = 0;

To reclaim all the disk space for this table, do this once a month:

ALTER TABLE HourCounter ENGINE=InnoDB;
ANALYZE TABLE HourCounter;

Give it a Try !!!

To give credit where is credit is due, I got the answer from page 140 of the Book

I have the book and refer to it well.

dnc_contacts would benefit from INDEX(group_id, contact) and/or INDEX(contact, group_id)