Optimizing MySQL is one of the most key skills for every server administrator and web developer, as the database is the biggest performance bottleneck in many applications. An unoptimized query or a missing index can take page load times from milliseconds to seconds and bring down the entire server in high traffic. In this hands-on guide, we'll cover everything from setting InnoDB engine parameters and index design to analyzing slow queries and regular maintenance with real commands.
The important thing is that database optimization is a data-driven process, not a guesswork. First, you need to identify slow queries and bottlenecks, then make targeted changes and measure the impact. Blindly changing hundreds of parameters without measuring them often causes more problems than helps.
Identify bottlenecks before optimizing MySQL
The first step in optimizing MySQL is to enable the slow query log. This log records any queries that take more than a certain amount of time and shows you where to focus:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';By setting long_query_time to 1 second, any query slower than one second will be logged. The log_queries_not_using_indexes parameter also records queries that do not use indexes; These are often the main culprits. After collecting the data, you can summarize the most frequent slow queries with the mysqldumpslow utility:
mysqldumpslow -s t -t 10 /var/log/mysql/slow.logReading the query execution plan with EXPLAIN
The most powerful tool for understanding why a query is slow is the EXPLAIN command. This command shows how MySQL intends to execute the query:
EXPLAIN SELECT * FROM orders WHERE customer_id = 4021 AND status = 'paid';In the output, pay attention to the column type and rows. If type is equal to ALL, it means that MySQL scans the entire table (full table scan), which is a disaster for large tables. The desired value is ref or range or const. The rows column shows the estimated number of rows checked; The less the better.
Proper design of indexes
Indexes are the most important tool for speeding up queries. An index acts like an index at the end of a book, allowing MySQL to go directly to the desired row without reading the entire table. But too much indexing is also harmful, because every write operation must also update the indexes.
Index on frequently used columns in WHERE and JOIN
Columns that are frequently used in WHERE, JOIN, and ORDER BY clauses are the best index candidates. For example:
CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);
CREATE INDEX idx_users_email ON users (email);The composite index on customer_id and status is very effective for the above query. The key point is the order of the columns in the composite index: the column used in the tie condition must come first. The "leftmost prefix" rule says that MySQL can only use the beginning of a composite index.
Pro tip: Avoid indexing on columns with little variation (like the gender column with two values). Such an index has little efficiency because half of the table still needs to be read. Indexes are more effective on columns with high cardinality such as email or ID.
Covering Index
If all the required columns of a query are available in the index itself, MySQL does not need to refer to the main table at all; This is called a cover index and it can make the query several times faster. For example, if you only want the status column, including it in the index removes the table reference.
Setting InnoDB engine parameters
The default and recommended MySQL engine today is InnoDB. Its most important parameter is innodb_buffer_pool_size, which determines how much data and index to cache in RAM. This is the single parameter that has the greatest impact on performance.
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1The general rule is to set the innodb_buffer_pool_size to about 70% of the total RAM on a dedicated database server. If your entire dataset fits in this buffer, practically all readings are done from RAM and the speed becomes significant.
Meaning of innodb_flush_log_at_trx_commit
This parameter determines the balance between data safety and speed. A value of 1 (the default) is the most secure and writes to disk after every transaction, but is slower. The value of 2 leaves the data to the operating system and is safe in case of software failure, but a second of data may be lost in a power outage; In return, write performance is greatly improved. For content sites where losing a second of data is not a disaster, a value of 2 is a good choice.
Optimization of queries and table structure
Even with the best indexes, a poorly written query can be slow. Some key principles in writing an optimal query:
- Avoid SELECT *: Select only the required columns so that less data is transferred and processed.
- Use LIMIT: When you only want a few rows, put LIMIT to make MySQL stop early.
- Avoid functions on columns indexed in WHERE: Writing WHERE YEAR(created_at) = 2026 invalidates the index; Use a date range instead.
- Do JOIN on indexed columns: JOIN without index is very slow on large tables.
- Choose the appropriate data type: Using INT instead of BIGINT when not needed, or VARCHAR of the appropriate length, improves volume and speed.
For example, instead of a suboptimal query that ignores the index, use an explicit range:
-- به جای این:
SELECT id FROM logs WHERE DATE(created_at) = '2026-07-16';
-- این را بنویسید:
SELECT id FROM logs WHERE created_at >= '2026-07-16 00:00:00' AND created_at < '2026-07-17 00:00:00';Partitioning large tables
When a table reaches tens of millions of rows, partitioning can help. By partitioning a table by date range or ID range, MySQL only reads the relevant partition. This technique is very effective for log and history tables, but is not worth the added complexity for small tables.
Management of connections and memory buffers
In addition to Buffer Pool, several other parameters have a direct impact on MySQL performance. The max_connections parameter determines how many simultaneous connections are allowed. Setting it to a very high number is dangerous, as each connection consumes memory and can exhaust the server's RAM. The estimated memory consumption formula of each connection includes several buffers such as sort_buffer_size and join_buffer_size:
max_connections = 200
sort_buffer_size = 2M
join_buffer_size = 2M
tmp_table_size = 64M
max_heap_table_size = 64M
table_open_cache = 4000A common mistake is to overinflate the buffers per connection. These buffers are allocated per connection, so a value of 16 MB for sort_buffer_size times 200 connections is 3.2 GB of RAM just for sorting. Moderate and reasonable values usually perform better than large values. If you have many sleeping connections, decrease the wait_timeout parameter so that abandoned connections are closed sooner.
Using temporary tables and on-disk sorting
When MySQL cannot perform a sort or group operation in memory, it performs it on disk, which is extremely slow. You can see how often this happens by looking at the state variables:
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';If created_tmp_disk_tables has a high ratio of total temporary tables, increasing tmp_table_size and max_heap_table_size can help. But remember that the root of the problem is often poorly designed queries; Optimizing the query itself is usually more effective than increasing the buffer.
Regular maintenance and database monitoring
Optimizing MySQL is not a one-time job; As the data grows, the statistics of the indexes become old and the tables become fragmented. Regular implementation of maintenance instructions is essential:
ANALYZE TABLE orders;
OPTIMIZE TABLE orders;The ANALYZE command updates the data distribution statistics so that the query optimizer can make better decisions. The OPTIMIZE command reclaims unused space and rebuilds indexes. Note that after deleting or updating a large amount of rows, the table will become fragmented, leaving more physical disk space than actual data; Regular execution of these commands frees up this space and improves reading performance. Also, keeping the table statistics up-to-date ensures that the query optimizer always chooses the best execution path and does not mistakenly proceed to a full table scan. For overall health monitoring, the free mysqltuner tool provides valuable recommendations based on the actual health of your server:
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl mysqltuner.pl| Indicator | good condition | need attention |
|---|---|---|
| Buffer Pool hit rate | above 95% | below 90% |
| Slow queries | near zero | Continuous increase |
| Full Table Scans | at least | High percentage |
| Simultaneous connections | Below 70% of the ceiling | near max_connections |
To run a high-speed database, you need a server with enough RAM and NVMe disk so that the Buffer Pool works properly and I/O is not a bottleneck. You can apply to buy a virtual server with dedicated resources and high-speed disk and follow more supplementary material in Rapido Server Articles.
Query cache and read and write strategy
In high-traffic architectures, even a perfectly optimized database may collapse under the heavy read load. In this case, adding an external cache layer like Redis to store the results of frequent queries is the most effective solution. Instead of running a heavy query for each page visit, the result is calculated once and stored in memory.
Another solution for scaling is read/write splitting using replicas. In this architecture, a master server performs all write operations and several read-only replicas divide the load of SELECT queries among themselves. This template is very effective for sites with a high read-to-write ratio, such as news and shopping sites, and allows for horizontal growth.
Choosing the right storage engine
Although InnoDB is the best choice for most applications, it's useful to know how it differs from other engines. InnoDB supports transaction, row-level locking, and foreign key, making it ideal for sensitive data and concurrent operations. Row-level locking means multiple users can modify different rows of the same table at the same time, whereas the old MyISAM engine locked the entire table, creating a serious bottleneck in high write traffic. For this reason, migrating legacy MyISAM tables to InnoDB is often one of the easiest ways to improve performance in legacy databases. In addition, InnoDB is more resistant to sudden server failure and minimizes the possibility of data loss or corruption with an automatic recovery mechanism after a power failure; A critical feature that MyISAM lacks and can lead to the corruption of the entire table in a sudden outage.
In summary, MySQL optimization is a combination of art and science: start with accurate metrics, focus on smart indexing and buffer pool tuning, write clean queries, and take maintenance seriously. With this approach, you can improve the performance of the database several times without necessarily paying more hardware.
Frequently asked questions
What is the most important parameter I should set in MySQL?
Definitely innodb_buffer_pool_size. This parameter determines how much data and index to cache in RAM. Setting it to around 70% RAM on a dedicated database server will usually provide the most performance improvement with the least amount of effort.
How do I know which queries are slow?
Enable the slow query log with appropriate long_query_time and after some time extract the most frequent slow queries with mysqldumpslow. Then use the EXPLAIN command to find out why they are slow and which index is low.
Is more index always better?
no Each index consumes disk space and slows write operations (INSERT/UPDATE/DELETE) as it must be updated. Only put indexes on the columns that are actually used in the queries and have high cardinality and delete the unused indexes.
How often should I run OPTIMIZE TABLE?
For tables that have a lot of writes and deletes, it makes sense to run monthly or after a large amount of data has been deleted. Note that OPTIMIZE can temporarily lock the table, so run it during low traffic hours and make sure you have a backup.
