MySQL Database Optimization: 6 Performance Tuning Techniques
Written By ROMAN AGABEKOV FEB 14, 2025
Last update | SEP 16, 2026
Database optimization is the practice of enhancing your MySQL database to improve its efficiency, speed, and reliability. The right technique depends on the problem: unnecessary query work, an inefficient access path, repeated calculations, or a storage concern.
This article explains individual tuning techniques and when to use each one for MySQL, with InnoDB-specific guidance for storage maintenance. For the full diagnostic sequence, including how to measure before you change anything and how to verify after, see the MySQL performance tuning guide.
This article explains individual tuning techniques and when to use each one for MySQL, with InnoDB-specific guidance for storage maintenance. For the full diagnostic sequence, including how to measure before you change anything and how to verify after, see the MySQL performance tuning guide.
Why Optimization Matters
Applications depend on databases to store and retrieve information. As data volumes, traffic and application features change, existing queries and configurations may no longer suit the workload. Common problems include:
- Slow queries: Queries may examine unnecessary rows, return unused data or repeat expensive calculations.
- Capacity pressure: More simultaneous requests can increase CPU, memory and storage demands, leading to longer response times.
- Lock contention: Concurrent transactions may wait for locks held by other transactions, delaying requests even when server resources are available.
Want to skip manual tuning?
Try our Free Online MySQL Query Optimizer for a quick check, or let Releem handle it continuously with our Automated SQL Query Optimization Feature.
Benefits of Effective Database Tuning
Tuning can improve performance and resource efficiency when the changes address a demonstrated bottleneck. Potential benefits include:
- Faster Query Execution: Removing unnecessary work or improving access paths can reduce query time and help applications respond faster. This responsiveness keeps users engaged, a critical need when 53% of mobile users abandon sites that take longer than 3 seconds to load.
- Cost Savings: Using resources more efficiently may postpone capacity upgrades or make a smaller database instance practical. Actual savings depend on your workload and infrastructure costs.
- Greater capacity: Reducing the work required per request can help the existing system handle more traffic before reaching its limits.
6 Key Database Tuning Techniques
1. Query Optimization
When to use it: a query requests data or performs work that the application does not need.
Queries determine how the database accesses and handles data. A query that seems acceptable in isolation can still deserve attention if it runs frequently.
Queries determine how the database accesses and handles data. A query that seems acceptable in isolation can still deserve attention if it runs frequently.
Target Specific Columns, Not Entire Tables
SELECT * retrieves every column. If the application needs only some of them, selecting those columns avoids returning unused data. If it needs every column, spelling out the same list does not reduce the result.
The SQL examples illustrate query choices. Try them on a small test dataset first; these SELECTs can scan tables and return many rows.
For example, if a sales table also contains notes or other fields that the screen does not use:
The SQL examples illustrate query choices. Try them on a small test dataset first; these SELECTs can scan tables and return many rows.
For example, if a sales table also contains notes or other fields that the screen does not use:
-- Instead of:
SELECT * FROM sales;
-- Use:
SELECT sale_id, date, amount, region, status FROM sales; Filter the Rows You Need
Use filters to express which rows belong in the result. This example returns orders placed since "January 1, 2023", for customers in the "USA". It assumes the application needs all order columns and `customers.id` is unique.
-- Query example: filter qualifying orders and customers
SELECT o.*
FROM orders o
JOIN (
SELECT id FROM customers
WHERE country = 'USA'
) c ON o.customer_id = c.id
WHERE o.order_date >= '2023-01-01';Putting the country filter inside a MySQL derived table does not force it to run first. MySQL may merge the derived table into the outer query. Use the execution plan to see how MySQL processes it.
Verify the Execution Plan
Use EXPLAIN FORMAT=JSON to inspect how MySQL plans the query. For a practical explanation of reading execution plans, see How to use EXPLAIN in MySQL.
EXPLAIN FORMAT=JSON
SELECT o.*
FROM orders o
JOIN (
SELECT id FROM customers
WHERE country = 'USA'
) c ON o.customer_id = c.id
WHERE o.order_date >= '2023-01-01'; Review the join order, each table's access method, the selected indexes and the estimated number of rows. If MySQL merges the derived table, the plan may not show a separate materialization step. That is expected optimizer behavior. Compare the plan with representative data and confirm that any query rewrite returns the same results.
Compare Subqueries and Joins
A join is one way to retrieve products in a department:
SELECT p.product_name
FROM products p
JOIN categories c ON p.category_id = c.category_id
WHERE c.department = 'Electronics'; A join is not automatically faster than a subquery. MySQL can transform eligible `IN` or `EXISTS` subqueries into semijoins. Before replacing an existence test with a join, check uniqueness: multiple matching category rows could duplicate a product in the join result.
2. Proper Indexing
When to use it: a necessary query spends too much work finding a small set of matching rows, and its plan suggests a better access path.
Poorly designed or excessive indexing can hinder performance, consume unnecessary storage, and negatively impact write operations. The solution is to determine when, where, and how to implement indexes for the workload.
Poorly designed or excessive indexing can hinder performance, consume unnecessary storage, and negatively impact write operations. The solution is to determine when, where, and how to implement indexes for the workload.
Strategic Indexing
The aim is to strike a balance between improving query speed and minimizing additional overhead. Indexes can help reads, but they use storage and require maintenance as indexed data changes.
Design indexes around the full query, including filters, joins and ordering. For composite indexes, column order matters. A table scan is not automatically a problem, especially when a query needs much of the table.
Review apparently unused indexes over a representative workload, including infrequent reports, before considering removal. There is no universal quarterly cleanup schedule.
Check the result: compare query latency and write performance. Before adding or removing an index, review existing indexes, required constraints, space and locking, and test the change on representative data.
Design indexes around the full query, including filters, joins and ordering. For composite indexes, column order matters. A table scan is not automatically a problem, especially when a query needs much of the table.
Review apparently unused indexes over a representative workload, including infrequent reports, before considering removal. There is no universal quarterly cleanup schedule.
Check the result: compare query latency and write performance. Before adding or removing an index, review existing indexes, required constraints, space and locking, and test the change on representative data.
3. Database Normalization and Denormalization
When to use it: the recurring cost comes from how data is stored, such as repeatedly calculating the same report totals.
Normalization organizes data into logical, narrowly focused tables to minimize redundancy. Keeping each fact in one place can simplify updates. Denormalization intentionally introduces redundant or derived data to reduce work for particular reads, at the cost of maintaining those additional values.
Normalization organizes data into logical, narrowly focused tables to minimize redundancy. Keeping each fact in one place can simplify updates. Denormalization intentionally introduces redundant or derived data to reduce work for particular reads, at the cost of maintaining those additional values.
Normalization and denormalization are tools, not rules. Start normalized for consistency, then denormalize where a measured workload justifies it. For example, a maintained daily summary may help a report that repeatedly aggregates individual sales.
Check the result: compare report time and update cost, and verify freshness and consistency. Fewer joins alone do not prove that a design is faster.
Check the result: compare report time and update cost, and verify freshness and consistency. Fewer joins alone do not prove that a design is faster.
4. Memory and Cache Management
When to use it: frequently reused data is repeatedly read from storage, or many requests need the same reusable result.
InnoDB uses a buffer pool to cache table and index pages. A cached page can avoid a storage read, but the query still has processing work to do. Consider increasing the buffer pool only when the workload would benefit and enough memory remains for connections, other operations and the operating system. An oversized pool can contribute to swapping.
InnoDB uses a buffer pool to cache table and index pages. A cached page can avoid a storage read, but the query still has processing work to do. Consider increasing the buffer pool only when the workload would benefit and enough memory remains for connections, other operations and the operating system. An oversized pool can contribute to swapping.
Application caches such as Redis or Memcached can reuse results across requests. This can reduce database reads when entries are reused, but it requires a policy for expiration, invalidation and acceptable staleness. It is different from caching database pages in the buffer pool.
Check the result: compare storage reads and latency for buffer-pool changes, or database query volume and response time for application caching. Also check memory pressure and whether cached answers remain correct.
Check the result: compare storage reads and latency for buffer-pool changes, or database query volume and response time for application caching. Also check memory pressure and whether cached answers remain correct.
5. Storage Maintenance When Needed
When to use it: you have a specific storage objective, such as reclaiming space after a large deletion, or a measured workload issue that maintenance could address.
Check Unused Space in Context
For a non-partitioned InnoDB table, this read-only query shows approximate data and index allocation alongside unused tablespace space. Replace the database and table names:
SELECT
TABLE_SCHEMA,
TABLE_NAME,
ROUND(DATA_LENGTH / 1048576, 2) AS data_mib,
ROUND(INDEX_LENGTH / 1048576, 2) AS indexes_mib,
ROUND(DATA_FREE / 1048576, 2) AS unused_tablespace_mib
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database'
AND TABLE_NAME = 'your_table'
AND ENGINE = 'InnoDB';Interpret the values carefully:
Use the result to investigate a specific storage concern, such as space remaining after a large deletion. A high value or a calculated percentage alone is not a reason to rebuild the table.
- data_mib: approximate space allocated to the clustered index, which contains the row data.
- indexes_mib: approximate space allocated to secondary indexes.
- unused_tablespace_mib: unused allocation in the table’s tablespace. For a shared tablespace, this space can belong to the shared allocation rather than this table alone.
Use the result to investigate a specific storage concern, such as space remaining after a large deletion. A high value or a calculated percentage alone is not a reason to rebuild the table.
Evaluate the Cost of Maintenance
InnoDB table optimization can involve rebuilding the table, consuming additional disk space and taking locks. Whether unused space returns to the operating system depends on the tablespace layout.
If a reviewed maintenance plan confirms that rebuilding is appropriate, MySQL supports:
If a reviewed maintenance plan confirms that rebuilding is appropriate, MySQL supports:
OPTIMIZE TABLE your_table;For an existing InnoDB table, this maps to an equivalent rebuild:
ALTER TABLE your_table FORCE;These commands can consume substantial CPU, I/O and temporary disk space. Avoid routine rebuilds without a specific expected benefit.
Before maintenance, review the expected benefit, free working space, locking, recovery plan and maintenance window.
Check the result: measure actual space reclaimed. If the objective is query performance, compare that workload before and after; reclaiming space does not establish a speedup.
Before maintenance, review the expected benefit, free working space, locking, recovery plan and maintenance window.
Check the result: measure actual space reclaimed. If the objective is query performance, compare that workload before and after; reclaiming space does not establish a speedup.
6. Monitoring and Troubleshooting
When to use it: you need to identify the cause of a slowdown or verify whether another technique helped.
Track query latency, execution frequency, lock waits, memory and storage activity over the same period. High memory usage alone does not establish a configuration problem, and lock waits require evidence about competing transactions.
For a small server-wide snapshot, use global status values:
Track query latency, execution frequency, lock waits, memory and storage activity over the same period. High memory usage alone does not establish a configuration problem, and lock waits require evidence about competing transactions.
- Track Performance Metrics
For a small server-wide snapshot, use global status values:
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Threads_running';Take two samples on the same server. Subtract the first sample from the second for both buffer-pool counters. `Innodb_buffer_pool_reads` counts reads that could not be served from the buffer pool. `Innodb_buffer_pool_read_requests` counts logical read requests.
If the increase in read requests is positive, divide the increase in reads by it to put those reads in context. Discard intervals spanning a restart or counter reset. Relate the result to workload and storage latency; there is no universal ratio to target.
`Threads_connected` includes idle connections. `Threads_running` counts non-sleeping threads, including those waiting for locks, and is not a measure of CPU utilization. See the MySQL status-variable definitions.
Check the result: compare the same metrics over comparable periods. Keep a change only when it improves the intended workload without unacceptable regressions.
If the increase in read requests is positive, divide the increase in reads by it to put those reads in context. Discard intervals spanning a restart or counter reset. Relate the result to workload and storage latency; there is no universal ratio to target.
`Threads_connected` includes idle connections. `Threads_running` counts non-sleeping threads, including those waiting for locks, and is not a measure of CPU utilization. See the MySQL status-variable definitions.
Check the result: compare the same metrics over comparable periods. Keep a change only when it improves the intended workload without unacceptable regressions.
FAQ:
Database Performance Tuning Techniques
Database Performance Tuning Techniques
Why should I avoid using SELECT * in my queries?
Avoid it when it returns columns the application does not need. Selecting fewer columns reduces returned data. Listing all the same columns does not produce that benefit.
How does filtering on the joined table improve performance?
A filter defines which rows qualify. Whether it reduces execution work depends on the plan and available indexes. Putting it inside a derived table does not force MySQL to process that table first.
When should I rebuild indexes?
Consider maintenance when evidence supports a specific storage or workload objective. Inserts, updates and deletes alone do not establish that a rebuild will improve performance. Review its space, locking and recovery requirements first.
Does DATA_FREE measure harmful fragmentation?
No. For InnoDB, it describes unused tablespace allocation, which can be shared. It does not establish that fragmentation is slowing a query.
How often should I perform database defragmentation?
There is no universal weekly, monthly or quarterly schedule. Use a demonstrated need and the expected operational cost to decide whether maintenance is worthwhile.
Avoid it when it returns columns the application does not need. Selecting fewer columns reduces returned data. Listing all the same columns does not produce that benefit.
How does filtering on the joined table improve performance?
A filter defines which rows qualify. Whether it reduces execution work depends on the plan and available indexes. Putting it inside a derived table does not force MySQL to process that table first.
When should I rebuild indexes?
Consider maintenance when evidence supports a specific storage or workload objective. Inserts, updates and deletes alone do not establish that a rebuild will improve performance. Review its space, locking and recovery requirements first.
Does DATA_FREE measure harmful fragmentation?
No. For InnoDB, it describes unused tablespace allocation, which can be shared. It does not establish that fragmentation is slowing a query.
How often should I perform database defragmentation?
There is no universal weekly, monthly or quarterly schedule. Use a demonstrated need and the expected operational cost to decide whether maintenance is worthwhile.
How Releem Helps
Releem is a database advisor that combines configuration tuning, query analysis, optimization recommendations, schema checks and monitoring in one workflow. You can investigate a performance problem, review and apply the proposed changes and compare the results rather than assuming that every adjustment will help.
If you are ready to bring these tasks together, try Releem today!
- MySQL Configuration Tuning: Analyzes workload metrics and recommends configuration changes. Review the recommended settings and decide when and how to apply them, then compare performance before and after the update.
- SQL Query Analytics: Groups similar queries and shows execution count, average execution time and total query time, helping you identify which queries deserve investigation first.
- SQL Query Optimization: Provides query and index recommendations for poorly performing queries. Use these recommendations to select improvements for testing and evaluate their effect on your workload.
- Schema Optimization: Checks database structures for potential schema and index issues, helping you identify improvements to review alongside query and configuration changes.
- MySQL Monitoring: Tracks database performance metrics, resource utilization and query performance over time, giving you context for investigating problems and checking the impact of tuning.
If you are ready to bring these tasks together, try Releem today!
Article by
- Founder & CEORoman Agabekov has 17 years of experience managing and optimizing MySQL and MariaDB in high-load environments. He founded Releem to automate routine database management tasks like performance monitoring, tuning, and query optimization. His articles share practical insights to help others maintain and improve their databases.
Get your own database advisor
Try Releem premium today for FREE!
Try Releem premium today for FREE!