MySQL 8.0/8.4 Performance Tuning Practice Guide

Executive Summary: The Three Pillars of MySQL Performance

Effective MySQL performance tuning for the 8.0 and 8.4 Long-Term Support (LTS) series is a systematic process based on three core pillars: Diagnosis, Engine Optimization, and Surgical SQL Tuning. Success is achieved by first identifying the high-impact queries (the 80/20 rule 1), allocating memory resources correctly (the InnoDB Buffer Pool is paramount 2), and then refining the execution paths through efficient indexing and query rewriting.

Phase I: Diagnosis and Workload Quantification

The first and most critical step is to objectively quantify the workload to ensure optimization efforts are directed toward the root causes of server load, not perceived bottlenecks.

1.1 Pinpoint High-Impact Queries

The priority must be placed on statements that contribute the highest total execution time to the server, regardless of how frequently they run.3

Enable the Slow Query Log: Configure the MySQL server to log all queries that exceed the time threshold defined by the long_query_time parameter.3
Audit with pt-query-digest: Use the Percona Toolkit’s pt-query-digest utility to analyze the Slow Query Log.3 This tool aggregates identical query patterns and ranks them by their cumulative response time.3 This report guides you to the queries responsible for the majority of the server burden.

1.2 Analyze the Execution Plan

Once a costly query is identified, dissect its execution mechanism using EXPLAIN.

Use EXPLAIN ANALYZE: Unlike the traditional EXPLAIN, EXPLAIN ANALYZE executes the query and provides detailed, actual performance metrics, including elapsed time for each stage.5
Check for Full Scans: In the execution plan, look for the type column values of ALL (Full Table Scan) or index (Full Index Scan).7 These are primary performance bottlenecks and mandate immediate attention to indexing.
Validate Optimizer Statistics: A significant mismatch between Estimated Rows and Actual Rows in the EXPLAIN ANALYZE output indicates that the Query Optimizer is using outdated statistics, leading to a poor query plan.5
- Action: Run ANALYZE TABLE periodically on frequently modified tables to update statistics and improve the Optimizer’s decision-making accuracy.1

Phase II: Engine Optimization (The InnoDB Core)

The single most impactful tuning action is the correct configuration of the InnoDB storage engine’s memory usage.

Configuration Area	Best Practice	Rationale & Impact	Reference
InnoDB Buffer Pool (IBP)	Set innodb_buffer_pool_size to 50% to 80% of dedicated system RAM.8	The IBP caches data and index pages. Maximizing its size drastically reduces reliance on slow disk I/O, leading to faster query response times.2	8
IBP Concurrency	If IBP is > 1GB, increase innodb_buffer_pool_instances.2	Divides the IBP into multiple segments, minimizing contention on internal structures and improving performance under high concurrent workloads.9	2
Query Cache (QC)	Disable or minimize the Query Cache.10	In write-heavy (OLTP) environments, any table modification invalidates all related cache entries, causing excessive lock contention and performance degradation .
Data Types	Select the smallest appropriate data type (e.g., MEDIUMINT instead of INT).11	Reduces the per-row memory footprint, allowing more data and index blocks to reside in the IBP, thus increasing memory efficiency.11	11

Phase III: Surgical SQL and Indexing Strategies

3.1 Advanced Indexing Strategy

Indexing accelerates reads but degrades writes; the strategy must be precise.

Prioritize High Selectivity: Create indexes only on columns with high selectivity (a large percentage of unique values) that are frequently used in WHERE, JOIN, ORDER BY, or GROUP BY clauses .
Achieve Covering Indexes: Design composite indexes that include all columns needed to satisfy the query (both for filtering and selection, i.e., SELECT columns) . This allows MySQL to fulfill the request entirely from the index structure, avoiding costly disk lookups of the main data table.12
Avoid Over-Indexing: Excessive indexing adds overhead to INSERT, UPDATE, and DELETE operations, as each index must be synchronously updated .

3.2 Query Rewriting and Filtering

Rewriting inefficient queries is often required to bypass Optimizer limitations.

Avoid SELECT *: Only retrieve the necessary columns to reduce memory consumption and data transfer volume .
Filter Early: Apply the most restrictive WHERE conditions as early as possible in the query process to minimize the working set size for subsequent operations .
Subqueries to JOINs: When performing UPDATE or DELETE statements that rely on a subquery (especially with IN), the MySQL Optimizer may not use the most advanced semijoin techniques.13 The high-performance solution is to explicitly rewrite these operations as multiple-table UPDATE or DELETE statements using a standard JOIN.13
Wildcard Search: Avoid leading wildcards (LIKE ‘%pattern’) as they prevent the use of standard B-tree indexes, forcing a full table scan . If necessary, implement a Full-Text Search (FTS) index or use suffix-based indexing .
Temporary Tables Indexing: If complex queries require temporary tables, indexes must be defined at the time of table creation; they cannot be added later.15 Ensure aggressive filtering is used to keep the intermediate results small and in memory.15

3.3 Large Data and Architectural Optimization

Partitioning for VLDBs: For Very Large Databases (VLDBs) with tables containing millions of rows, use partitioning (e.g., RANGE partitioning) to logically divide the table.16
Effective Partition Pruning: The partition key must align with the columns used in your WHERE clauses.17 This allows MySQL to perform Partition Pruning, where it automatically excludes non-relevant partitions from the scan, dramatically reducing I/O.16
Regular Maintenance: Routinely perform maintenance tasks like defragmenting tables and rebuilding indexes, as high volumes of DML operations lead to fragmentation and suboptimal disk access patterns over time .

MySQL 9.0 Reference Manual :: 10.2.1 Optimizing SELECT Statements， https://dev.mysql.com/doc/refman/9.0/en/select-optimization.html
MySQL Performance: InnoDB Buffers & Directives - Liquid Web, https://www.liquidweb.com/blog/mysql-performance-innodb-buffers-directives/
Using Slow Query Log to Find High Load Spots in MySQL - Percona https://www.percona.com/blog/identifying-high-load-mysql-slow-query-log-pt-query-digest/
Oracle Autonomous Database Strategy https://www.oracle.com/oce/dc/assets/CONTC552D01FF25448BFA6CFA4E2D7758F6C/native/oracle-autonomous-database-strategy-wp.pdf?elqTrackId=4773d4684589422bbc56a54717d6b82c&elqaid=104046&elqat=2
Mastering EXPLAIN ANALYZE in MySQL: Optimize Your Queries Like a Pro! - Dev Cookies, https://devcookies.medium.com/mastering-explain-analyze-in-mysql-optimize-your-queries-like-a-pro-7f8b3ea28bb0
Building Snowflake Native App Framework: Guide & Best Practices, https://www.bluent.com/blog/building-snowflake-native-apps
MySQL 8.4 Reference Manual :: 10.8.1 Optimizing Queries with EXPLAIN, https://dev.mysql.com/doc/refman/8.4/en/using-explain.html
innodb_buffer_pool_size - MySQL Performance - LinuxBlog.io, https://linuxblog.io/innodb_buffer_pool_size-mysql-performance/
MySQL Partitioning for Performance Optimization: Usage & Examples - DataCamp, https://www.datacamp.com/doc/mysql/mysql-partitioning
Rick’s RoTs -- Rules of Thumb for MySQL, https://mysql.rjweb.org/doc.php/ricksrots
What are the top 6 MySQL alternatives? - IONOS, https://www.ionos.com/digitalguide/server/know-how/mysql-alternatives/
MySQL Query Optimization: Faster Performance & Data Retrieval - Airbyte, https://airbyte.com/data-engineering-resources/optimizing-mysql-queries
Migrate your SQL Server database to Cloud SQL for PostgreSQL - Google Cloud Documentation, https://docs.cloud.google.com/database-migration/docs/sqlserver-to-csql-pgsql/guide
Azure MySQL: MySQL as a Service vs. Self-Managed in the Cloud - NetApp BlueXP, https://bluexp.netapp.com/blog/azure-cvo-azure-mysql-mysql-as-a-service-vs.-self-managed-in-the-cloud
SQL Server to Postgres – A Step-by-Step Migration Journey - BryteFlow, https://bryteflow.com/sql-server-vs-postgres-how-to-migrate-sql-to-postgresql/
Autonomous Database Pricing | Oracle Middle East Regional, https://www.oracle.com/middleeast/autonomous-database/pricing/
What alternatives to SQL are there? - IONOS, https://www.ionos.com/digitalguide/server/know-how/the-best-sql-alternatives/

What is SQLFlash?

SQLFlash is your AI-powered SQL Optimization Partner.

Based on AI models, we accurately identify SQL performance bottlenecks and optimize query performance, freeing you from the cumbersome SQL tuning process so you can fully focus on developing and implementing business logic.