I have just moved from running my local MySQL database from using MAMP-supported MYSQL v5.7.30 to a "normal" local MySQL installation of v8.0.22. While the reason for the switch was that I wanted to use CTEs, I was also hoping for a performance increase, as is frequently reported.
While the performance ist slightly better (10-20%) for most of my queries, the performance for a simple SELECT count(*) from mytable; takes about twice as long: 3.8s on MAMP-MySQL 5.7.30 vs. 7.5s on MySQL 8.0.22. This is on a table with 15m rows and 12 columns, 1 PK, 1 indexed column (besides the PK), no FK.
The tables should be exactly the same as I migrated them using the MySQL Workbench Migration Wizard.
Searching Stackoverflow for possible performance tweaks it looks like the usual suspects may not be set to ideal values (using the out-of-the-box settings of MySQL), but they are at least identical between my two databases:
SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; # 134217728 = 128MB
SHOW VARIABLES LIKE 'innodb_log_file_size'; # 50331648 = 48MB
SHOW VARIABLES LIKE 'innodb_buffer_pool_instances'; # 1
So the question is: Why is the performance for COUNT(*) statements so much worse, and how can I get the performance out of MySQL 8.0 that I get out of MySQL 5.7?
As this was asked in the comments: For both 5.7 and 8.0 I use the same machine, which is a fairly recent laptop with a 2.2 GHz intel i7 processor and 16GB of RAM, running macOS Catalina.
Any pointers are greatly appreciated!
EDIT: As requested in the comments, here are a couple more details:
1 - SHOW CREATE TABLE mytable
For 5.7:
CREATE TABLE `mytable` (
`var1` int(11) NOT NULL,
`var2` datetime(6) NOT NULL,
`var3` datetime(6) NOT NULL,
`var4` datetime(6) DEFAULT NULL,
`var5` datetime(6) DEFAULT NULL,
`var6` datetime(6) DEFAULT NULL,
`var7` decimal(6,2) DEFAULT NULL,
`var8` text,
`var9` text,
`var10` text,
`var11` text,
`var12` int(11) DEFAULT NULL,
PRIMARY KEY (`var1`),
KEY `idx_var12` (`var12`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
For 8.0:
CREATE TABLE `mytable` (
`var1` int NOT NULL,
`var2` datetime(6) NOT NULL,
`var3` datetime(6) NOT NULL,
`var4` datetime(6) DEFAULT NULL,
`var5` datetime(6) DEFAULT NULL,
`var6` datetime(6) DEFAULT NULL,
`var7` decimal(6,2) DEFAULT NULL,
`var8` text,
`var9` text,
`var10` text,
`var11` text,
`var12` int DEFAULT NULL,
PRIMARY KEY (`var1`),
KEY `idx_var12` (`var12`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
2 - EXPLAIN SELECT count(*) from mytable;
For both 8.0 and 5.7:
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'mytable', NULL, 'index', NULL, 'idx_var12', '5', NULL, '15480019', '100.00', 'Using index'
3 - SHOW FULL PROCESSLIST;
For 5.7:
# Id, User, Host, db, Command, Time, State, Info
'682', 'root', 'localhost:64503', 'testdb', 'Sleep', '178', '', NULL
'683', 'root', 'localhost:64504', 'testdb', 'Query', '0', 'starting', 'SHOW FULL PROCESSLIST'
For 8.0:
# Id, User, Host, db, Command, Time, State, Info
'5', 'event_scheduler', 'localhost', NULL, 'Daemon', '1545', 'Waiting on empty queue', NULL
'8', 'root', 'localhost:65524', 'my_schema', 'Query', '0', 'init', 'SHOW FULL PROCESSLIST'
'9', 'root', 'localhost:65525', 'my_schema', 'Sleep', '284', '', NULL
4 - SHOW TABLE STATUS WHERE name = 'mytable';
For 5.7:
# Name, Engine, Version, Row_format, Rows, Avg_row_length, Data_length, Max_data_length, Index_length, Data_free, Auto_increment, Create_time, Update_time, Check_time, Collation, Checksum, Create_options, Comment
'mytable', 'InnoDB', '10', 'Dynamic', '15480019', '168', '2611986432', '0', '261898240', '6291456', NULL, '2020-11-17 23:03:04', NULL, NULL, 'utf8_general_ci', NULL, '', ''
For 8.0:
# Name, Engine, Version, Row_format, Rows, Avg_row_length, Data_length, Max_data_length, Index_length, Data_free, Auto_increment, Create_time, Update_time, Check_time, Collation, Checksum, Create_options, Comment
'mytable', 'InnoDB', '10', 'Dynamic', '14009911', '148', '2081423360', '0', '313507840', '6291456', NULL, '2020-11-22 21:03:29', '2020-11-22 21:17:55', NULL, 'utf8_general_ci', NULL, '', ''
