How to optimize COUNT(*) performance on InnoDB by using index

by

As of MySQL 5.1.6 you can use the Event Scheduler and insert the count to a stats table regularly.

First create a table to hold the count:

CREATE TABLE stats (
`key` varchar(50) NOT NULL PRIMARY KEY,
`value` varchar(100) NOT NULL);

Then create an event to update the table:

CREATE EVENT update_stats
ON SCHEDULE
  EVERY 5 MINUTE
DO
  INSERT INTO stats (`key`, `value`)
  VALUES ('data_count', (select count(id) from data))
  ON DUPLICATE KEY UPDATE value=VALUES(value);

It’s not perfect but it offers a self contained solution (no cronjob or queue) that can be easily tailored to run as often as the required freshness of the count.

More Related Contents:

  1. MySQL foreign key constraints, cascade delete
  2. How do I see all foreign keys to a table or column?
  3. How do I quickly rename a MySQL database (change schema name)?
  4. How to debug Lock wait timeout exceeded on MySQL?
  5. 1114 (HY000): The table is full
  6. TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT maximum storage sizes
  7. Howto: Clean a mysql InnoDB storage engine?
  8. How to shrink/purge ibdata1 file in MySQL
  9. MySQL InnoDB not releasing disk space after deleting data rows from table
  10. Why is MySQL InnoDB insert so slow?
  11. How to convert all tables from MyISAM into InnoDB?
  12. Prevent auto increment on MySQL duplicate insert
  13. UUID performance in MySQL?
  14. MySQL InnoDB foreign key between different databases
  15. Force InnoDB to recheck foreign keys on a table/tables?
  16. Bogus foreign key constraint fail
  17. What is MySQL’s default ON DELETE behavior?
  18. How do I repair an InnoDB table?
  19. How to enable INNODB in mysql
  20. Is there any performance gain in indexing a boolean field?
  21. How to disable index in innodb
  22. Why does MySQL autoincrement increase on failed inserts?
  23. How can I check MySQL engine type for a specific table?
  24. MySQL InnoDB: autoincrement non-primary key
  25. How do I rename a MySQL database (change schema name)?
  26. How to regex in a MySQL query
  27. Why doesn’t MySQL’s MyISAM engine support Foreign keys?
  28. Database corruption with MariaDB : Table doesn’t exist in engine
  29. “Unknown column in ‘field list'”, but column does exist
  30. Should I always prefer MySQL InnoDB over MyISAM?

Leave a Comment