Recently, I had to run a large MySQL script (Heavy.sql) on two different servers:
Server OS MySQL Version Runtime
Server 1 RHEL 7.9 MySQL 5.7.31 53 minutes
Server 2 RHEL 8.6 MySQL 8.0.26 12 minutes
Both machines had identical hardware:
4 vCPUs
30 GB RAM
NVMe SSD
Same dataset
Same script
Yet MySQL 5.7 was 4× slower.
This post explains why, how I analyzed it, and how I eventually brought the runtime down to 15–18 minutes — on the same old MySQL 5.7 server.
🧩 The Root Cause: MySQL 5.7 vs MySQL 8 Query Engine
The script involved:
Large CREATE TABLE AS SELECT
Deep nested VIEWs
Multiple LEFT JOINs
GROUP_CONCAT aggregations
Big temporary tables
Millions of rows
What I found:
❌ MySQL 5.7 expands VIEWs literally
When a query uses multiple views, MySQL 5.7:
expands the view SQL text
builds huge temp tables
re-applies filters later
cannot push predicates down
This results in repeated full-table scans.
✅ MySQL 8 optimizes VIEWs
MySQL 8 can:
push WHERE conditions inside views
push LIMIT
prune unused columns
choose better join orders
This is why the same script finishes 4× faster on MySQL 8.
⚙️ Step 1 — Fix MySQL Configuration (my.cnf)
On Server 1 (MySQL 5.7), I applied the following tuning:
[mysqld]
InnoDB memory
innodb_buffer_pool_size = 22G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 1G
innodb_flush_method = O_DIRECT
Temp tables
tmp_table_size = 1G
max_heap_table_size = 1G
join_buffer_size = 64M
sort_buffer_size = 8M
Avoid GROUP_CONCAT truncation
group_concat_max_len = 16M
Misc
skip-name-resolve
table_open_cache = 4000
open_files_limit = 65535
Result:
~30% performance improvement
(53 minutes → ~37 minutes)
Top comments (0)