Change MyISAM tables to InnoDB and handle SELECT COUNT(*) situation

Its a known problem that changing the Storage Engine from MyISAM to InnoDB can cause some problems [ 1 ] if you have queries of this type:

SELECT COUNT(*) from table;

Luckily this query happens rarely and if, the query can be easily omitted or worked around by guesstimating the amount of rows in the table. For example with:

SHOW TABLE STATUS LIKE 'test';

But in some rare cases customer really needs these values for some reasons. To not exhaust the resources of the server with this query which can be fired quite often in some cases we make use of the materialized views/shadow table technique [ 2 ].

The following example illustrates how to do this.

Our original situation

We have an offer table which is feed by a host system:

CREATE TABLE offer (
  id   int unsigned NOT NULL AUTO_INCREMENT
, `type` CHAR(3) NOT NULL DEFAULT 'AAA'
, data varchar(64) DEFAULT NULL
, PRIMARY KEY (`id`)
, INDEX (type)
) ENGINE=InnoDB;

INSERT INTO offer VALUES (NULL, 'AAA', 'Blablabla');
INSERT INTO offer VALUES (NULL, 'ABC', 'Blablabla');
INSERT INTO offer VALUES (NULL, 'ZZZ', 'Blablabla');

The query we want to perform looks like this:

SELECT COUNT(*) FROM offer;

This query becomes expensive when you have zillions of rows in your table.v

The work around

To work around the problem we create a counter table where we count the rows which are inserted, updated or deleted on the offer table.

CREATE TABLE counter (
  `type` char(3) NOT NULL DEFAULT 'AAA'
, `count` MEDIUMINT UNSIGNED NOT NULL DEFAULT 0
, `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
, PRIMARY KEY (type)
) ENGINE=InnoDB;

To fill this counter table we need an initial snapshot:

INSERT INTO counter
SELECT type, COUNT(*), NULL
  FROM offer
 GROUP BY type;

SELECT * FROM counter;
SELECT COUNT(*) FROM counter;

Update the counter table

To keep the counter table up-to-date we need the following 3 triggers:

DROP TRIGGER IF EXISTS insert_offer_trigger;

delimiter //

CREATE TRIGGER insert_offer_trigger
AFTER INSERT ON offer FOR EACH ROW
BEGIN
  INSERT INTO counter
  VALUES (NEW.type, 1, NULL)
  ON DUPLICATE KEY
  UPDATE count = count + 1, ts = CURRENT_TIMESTAMP();
END;
//

delimiter ;


DROP TRIGGER IF EXISTS update_offer_trigger;

delimiter //

CREATE TRIGGER update_offer_trigger
AFTER UPDATE ON offer FOR EACH ROW
BEGIN
  IF NEW.type = OLD.type THEN
    UPDATE counter SET ts = CURRENT_TIMESTAMP() WHERE type = NEW.type;
  ELSE
    UPDATE counter
       SET count = count - 1, ts = CURRENT_TIMESTAMP()
     WHERE type = OLD.type;
    INSERT INTO counter
    VALUES (NEW.type, 1, NULL)
    ON DUPLICATE KEY
    UPDATE count = count + 1, ts = CURRENT_TIMESTAMP();
  END IF;
END;
//

delimiter ;


DROP TRIGGER IF EXISTS delete_offer_trigger;

delimiter //

CREATE TRIGGER delete_offer_trigger
AFTER DELETE ON offer FOR EACH ROW
BEGIN
  UPDATE counter SET count = count - 1 WHERE type = OLD.type;
END;
//

delimiter ;

Now we can test some cases and compare the results of both tables:

INSERT INTO offer VALUES (NULL, 'AAA', 'Blablabla');
INSERT INTO offer VALUES (NULL, 'AAA', 'Blablabla');

-- Single offer change
UPDATE offer SET data = 'Single offer change' WHERE id = 2;

-- Multi offer change
UPDATE offer SET data = 'Multi offer change' WHERE type = 'AAA';

-- Single offer delete
DELETE FROM offer WHERE id = 1;

-- REPLACE (= DELETE / INSERT)
REPLACE INTO offer VALUES (3, 'ZZZ', 'Single row replace');

-- New type
INSERT INTO offer VALUES (NULL, 'DDD', 'Blablabla');

-- Change of type
UPDATE offer SET type = 'ZZZ' where id = 2;

-- Change of type to new type
UPDATE offer SET type = 'YYY' where id = 3;

-- INSERT on DUPLICATE KEY UPDATE
INSERT INTO offer VALUES (7, 'DDD', 'ON DUPLICATE KEY UPDATE')
ON DUPLICATE KEY UPDATE type = 'DDD', data = 'INSERT ON DUPLICATE KEY';
INSERT INTO offer VALUES (7, 'DDD', 'ON DUPLICATE KEY UPDATE')
ON DUPLICATE KEY UPDATE type = 'DDD', data = 'UPDATE ON DUPLICATE KEY UPDATE';

SELECT * FROM offer;
SELECT COUNT(*) FROM offer;
SELECT * FROM counter;
SELECT SUM(count) FROM counter;

This solution has the advantage that we get also a very fast response on the number of rows for a specific order type. Which would be also expensive for MyISAM tables...