Why MySQL 8.0 Queries Run Ten Times Slower Than 5.6 – A Deep Dive

Background

The user upgraded an RDS MySQL instance from 5.6 to 8.0 and observed that an identical SQL statement took more than ten times longer to execute. The query is essentially a full‑table scan with an ORDER BY on a non‑indexed column, so such a huge performance gap was unexpected.

Problem Analysis

Initial comparison of EXPLAIN output for both versions showed identical plans: a full‑table scan plus filesort.

The Optimizer Trace also revealed no major differences, but profiling indicated a significant increase in execution time during the executing phase on 8.0.

Further profiling with perf highlighted that the function row_sel_store_mysql_rec consumed a much larger portion of CPU time on 8.0 (63%) compared to 5.6 (28%). Additional investigation showed that 8.0 performed many more memory‑copy operations.

Reproduction

To reproduce the issue, the author created a simplified test table and data set on both MySQL 5.6 and 8.0 (community versions 5.6.51 and 8.0.38). The test query SELECT * FROM t1 ORDER BY paytime LIMIT 1; took 0.14 s on 5.6 and 1.14 s on 8.0.

-- Create table
create table t1 (
  id int auto_increment primary key,
  col2 int,
  col3 char(255),
  col4 varchar(8192),
  paytime int unsigned DEFAULT NULL
) ENGINE=INNODB;

-- Insert data (300k rows)
DELIMITER //
CREATE PROCEDURE insert_data()
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < 300000 DO
    INSERT INTO t1 VALUES (null, i, repeat('a',255), repeat('a',8192), FLOOR(RAND()*300000));
    SET i = i + 1;
  END WHILE;
END//
CALL insert_data();

-- Query
SELECT * FROM t1 ORDER BY paytime LIMIT 1;

Perf Findings

Both versions spent ~80% of CPU time in ha_rnd_next (row fetching). However, 8.0 spent 63% of that time inside row_sel_store_mysql_rec, while 5.6 spent only 28%.

The btr_copy_externally_stored_field function, responsible for handling overflow pages, was called 300 000 times on 8.0 (matching the full table scan) but only once on 5.6 (matching the LIMIT 1).

bpftrace Investigation

Using bpftrace, the author traced the execution of row_sel_store_mysql_rec and observed that its duration histogram on 8.0 was roughly double that on 5.6.

# Trace row_sel_store_mysql_rec execution
bpftrace -e '
  uprobe:/path/to/mysql-8.0.38/bin/mysqld:_Z23row_sel_store_mysql_recPhP14row_prebuilt_tPKhPK8dtuple_tbPK12dict_index_tS9_PKmbPN3lob11undo_vers_tERP16mem_block_info_t {
    @start[tid] = nsecs;
  }
  uretprobe:/path/to/mysql-8.0.38/bin/mysqld:_Z23row_sel_store_mysql_recPhP14row_prebuilt_tPKhPK8dtuple_tbPK12dict_index_tS9_PKmbPN3lob11undo_vers_tERP16mem_block_info_t /@start[tid]/ {
    @duration = hist(nsecs - @start[tid]);
    delete(@start[tid]);
    @count++;
  }
  END {
    printf("Function execution count: %d
", @count);
    print(@duration);
  }
'

Source‑Code Analysis

Reading the source of both versions shows that row_sel_store_mysql_rec behaves similarly: it converts InnoDB rows to server rows based on a bitmap read_set. In 5.6, the filesort implementation clears read_set before sorting and only re‑marks fields actually needed for the ORDER BY (e.g., the primary key and paytime). Consequently, overflow columns like col4 are not converted during sorting.

In 8.0, this special handling of read_set was removed, so the conversion runs for every column during the filesort phase, dramatically increasing CPU usage when overflow columns are present.

Conclusion

The performance regression stems from MySQL 8.0’s filesort no longer resetting read_set, causing unnecessary conversion of large overflow columns during sorting. This issue persists in MySQL 8.0, 8.4, and 9.0, while MySQL 5.7 and earlier versions do not exhibit it. Users should consider indexing strategies or avoid SELECT * on tables with large overflow columns until the bug is addressed.