How to increase 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, not circumvent doing a 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)

ALTER 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 (Was 128Mb) (Restarted 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 SELECT COUNT(PK) or SELECT COUNT(*) on a MyISAM table with no where clause is a very specific situation because it does not count each row and so is very fast. The same query on InnoDB is slow because it DOES count each row.

However as soon as a where clause comes into play on an indexed field, the performance of InnoDB appears to outshine MyIsam. Seeing as this is 99% of use cases.....