How to improve select performance on a 15 million row MyISAM table converted to InnoDB

Question

MySQL version 8.0.32-0ubuntu0.20.04.2

I'm trying to improve SELECT performance, rather than circumvent executing the SELECT.

CREATE TABLE big_table (
pk INT AUTO_INCREMENT PRIMARY KEY,
field1 VARCHAR(255),
field2 VARCHAR(255),
field3 mediumtext,
field4 BIGINT,
KEY idx_field4 (field4)
) ENGINE=MyISAM CHARSET=utf8mb3;

Insert 15 million rows.

SELECT COUNT(pk) FROM big_table;
+---------------+
| count(pk)     |
+---------------+
|      15911974 |
+---------------+
1 row in set (0.57 sec)

Change table big_table ENGINE=INNODB;

SELECT COUNT(pk) FROM big_table;
+---------------+
| count(pk)     |
+---------------+
|      15911974 |
+---------------+
1 row in set (10.23 sec)

**Set innodb_buffer_pool_size=8G (originally 128Mb) (restart MySQL)**

SELECT COUNT(pk) FROM big_table;
+---------------+
| count(pk)     |
+---------------+
|      15911974 |
+---------------+
1 row in set (1 min 18.67 sec)

Answer 1

It turns out that executing SELECT COUNT(PK) or SELECT COUNT(*) on a MyISAM table without a where clause is a very special case, as it does not count every row and is therefore very fast. The same query on InnoDB is slow because it literally counts every row.

However, once the where clause comes into play on the indexed fields, InnoDB seems to outperform MyIsam. It seems like this is 99% of the use cases...