Optimizing COUNT(*) Performance on InnoDB with Indices

When dealing with large but narrow InnoDB tables, executing COUNT(*) queries can be notoriously slow. This was encountered in a scenario where a table consisting of ~9 million records resulted in a 6 second COUNT(*) operation.

According to MySQL documentation, forcing InnoDB to use an index for counting operations can yield significant performance gains. This is achieved by using the USE INDEX (index_name) syntax in the query.

In the given example, the following query was employed:

SELECT COUNT(id) FROM perf2 USE INDEX (PRIMARY);

However, despite using the index, the performance remained abysmal. Seeking further troubleshooting options, it was discovered that MySQL 5.1.6 introduced an efficient solution involving the Event Scheduler and statistical caching.

Event Scheduler and Statistical Caching

By utilizing the Event Scheduler and maintaining a stats table, the COUNT(*) operation can be significantly optimized. The process entails creating a stat table to store the count data:

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

Subsequently, an event is created to regularly update the stats table with the current count:

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);

This self-contained solution allows for customizable refresh intervals, ensuring the accuracy and freshness of the stored count. While it may not be perfect, it offers considerable performance enhancements compared to traditional methods.