Why COUNT(*) May Be Slower Than COUNT(1) in MySQL InnoDB

Conclusion

count(*) ≈ count(1) > count(id) > count(indexed column) > count(unindexed column)

Experiment Setup

A table hospital_statistics_data with about 1 000 000 rows is created in MySQL 5.7 using the InnoDB engine. The table initially has only a clustered primary key ( pk_id).

CREATE TABLE `hospital_statistics_data` (
  `pk_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `id` varchar(36) NOT NULL COMMENT 'foreign key',
  `hospital_code` varchar(36) NOT NULL COMMENT 'hospital code',
  `biz_type` tinyint NOT NULL COMMENT '1 service flow 2 management effect',
  `item_code` varchar(36) DEFAULT NULL COMMENT 'assessment item code',
  `item_name` varchar(64) DEFAULT NULL COMMENT 'assessment item name',
  `item_value` varchar(36) DEFAULT NULL COMMENT 'assessment result',
  `is_deleted` tinyint DEFAULT NULL COMMENT 'deleted flag',
  `gmt_created` datetime DEFAULT NULL COMMENT 'creation time',
  `gmt_modified` datetime DEFAULT NULL COMMENT 'modification time',
  `gmt_deleted` datetime(3) DEFAULT '9999-12-31 23:59:59.000' COMMENT 'deletion time',
  PRIMARY KEY (`pk_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='hospital statistics data';

1️⃣ Only the clustered primary key

Running EXPLAIN SELECT COUNT(*) FROM hospital_statistics_data; shows that MySQL uses the primary key index (type=index, key=PRIMARY, key_len=8).

2️⃣ Adding a non‑clustered index on hospital_code

After

ALTER TABLE hospital_statistics_data ADD INDEX idx_hospital_code(hospital_code);

, the table has two indexes. The same EXPLAIN now reports type=index, key=idx_hospital_code, key_len=146.

3️⃣ Adding a second non‑clustered index on biz_type

After

ALTER TABLE hospital_statistics_data ADD INDEX idx_biz_type(biz_type);

, the table now has three indexes. The EXPLAIN output switches to using idx_biz_type (key_len=1).

Observations

COUNT(*) and COUNT(1) both end up using the same index chosen by the optimizer.

The optimizer prefers the index with the smallest key length (i.e., the most compact index).

COUNT(id) uses the primary key because id is not indexed.

COUNT on an indexed column uses that specific index; COUNT on an unindexed column forces a full table scan.

Necessary Knowledge Points

MySQL consists of a service layer (optimizer) and an engine layer (InnoDB).

All SQL statements are first optimized; the optimizer evaluates cost and rules to choose an execution plan.

The execution plan reflects the optimizer's best‑guess path, not an absolute guarantee.

InnoDB has a clustered primary key; secondary indexes store only the primary key value.

Index selection depends on data distribution; large result sets may cause the optimizer to ignore an index.

Why COUNT(*) Can Be Slow

Even when an index is used, COUNT(*) must iterate over every row that matches the query. For very large tables this full‑index scan can still be expensive because MySQL treats * as a constant (0) and increments a counter for each row.

InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) in the same way. There is no performance difference.

Therefore, the perceived speed difference in the article comes from the specific index chosen and the size of the index key, not from a fundamental difference between COUNT(*) and COUNT(1).

Execution Process of COUNT(*) in InnoDB

The server layer creates a counter variable.

It requests the first record from InnoDB.

InnoDB returns the first secondary‑index record (no table‑row lookup needed).

Because * is treated as a non‑NULL constant, the server increments the counter.

The server repeatedly asks InnoDB for the next index record via the next_record pointer.

Each retrieved record causes the counter to increase by one.

The loop continues until InnoDB reports no more records.

The final count is sent back to the client.

Understanding these steps helps explain why index choice and key length affect the overall performance of COUNT operations.