What are the best MySQL optimize per­for­mance tech­niques for Linux?

The per­for­mance of a database has a major impact on the speed and stability of web ap­pli­ca­tions. Both MariaDB and MySQL offer a wide range of op­ti­miza­tion options — from con­fig­u­ra­tion ad­just­ments to indexing, caching, and repli­ca­tion. With targeted op­ti­miza­tions, you can use database resources more ef­fi­cient­ly and sig­nif­i­cant­ly reduce query response times.

Why should you optimize MariaDB and MySQL?

Op­ti­miz­ing MariaDB and MySQL makes sense because un­op­ti­mized databases can quickly reach their limits. Poor per­for­mance can lead to long load times, timeouts, or even system failures.

Es­pe­cial­ly with large amounts of data or many si­mul­ta­ne­ous accesses, every in­ef­fi­cient query strains the CPU and RAM. Storing redundant data or missing indexes also neg­a­tive­ly impact speed. An optimized database reduces system load, improves scal­a­bil­i­ty, and ensures ap­pli­ca­tions run stably even under high load.

How can MySQL/MariaDB be optimized?

There are several ways to optimize a MariaDB or MySQL database on a Linux server. These include con­fig­u­ra­tion ad­just­ments, index op­ti­miza­tion, query im­prove­ments, InnoDB tuning, as well as im­ple­ment­ing caching or repli­ca­tion. The following sections outline the most important measures.

Option 1: Identify slow queries

An important step in op­ti­miz­ing MySQL or MariaDB is iden­ti­fy­ing slow or in­ef­fi­cient queries. Even a single poorly struc­tured query can neg­a­tive­ly impact the per­for­mance of the entire database.

MySQL and MariaDB can be con­fig­ured to log all queries that exceed a specified execution time. This allows you to track slow queries and optimize them as needed.

To enable slow query logging, log in to MySQL or MariaDB:

mysql -u root -p

Enter the following command to enable logging:

SET GLOBAL slow_query_log = 'ON';

The default threshold is 10 seconds. Use the following command to enable logging for any query that takes longer than 1 second:

SET GLOBAL long_query_time = 1;

Queries that take longer than 1 second will be logged under /var/lib/mysql/hostname-slow.log.

Mon­i­tor­ing tools like mysqltuner or performance_schema can also be used and provide valuable insights to identify which queries can be optimized.

Option 2: Adjust InnoDB con­fig­u­ra­tion

Con­fig­ur­ing MariaDB or MySQL is one of the most effective ways to sus­tain­ably improve database per­for­mance. Many default in­stal­la­tions use generic settings designed for small test en­vi­ron­ments rather than pro­duc­tion systems handling numerous queries. By fine-tuning InnoDB pa­ra­me­ters, you can ensure that the database uses available resources more ef­fi­cient­ly.

Key pa­ra­me­ters include:

• innodb_flush_log_at_trx_commit: Balances per­for­mance and re­li­a­bil­i­ty. The default value 1 writes each trans­ac­tion im­me­di­ate­ly to disk, ensuring maximum data safety but po­ten­tial­ly reducing per­for­mance under heavy load. Setting it to 2 decreases I/O op­er­a­tions sig­nif­i­cant­ly, with a small risk of data loss in case of a crash.
• innodb_log_file_size: Controls the size of InnoDB log files. Larger values allow more trans­ac­tions to be buffered in memory before being written to disk, improving write per­for­mance.
• innodb_file_per_table: Creates a separate ta­ble­space file for each InnoDB table. This helps manage large tables more easily, reduces frag­men­ta­tion in the shared ta­ble­space, and can improve backup per­for­mance.
• innodb_buffer_pool_size: De­ter­mines how much memory is allocated to store data and indexes. For optimal per­for­mance, this should typically be set to 50–80% of the available RAM.
• innodb_flush_method: Defines how InnoDB writes data and logs to disk, which can affect I/O per­for­mance.

An example con­fig­u­ra­tion in my.cnf could look like this:

[mysqld]
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 512M
innodb_file_per_table = 1
innodb_buffer_pool_size = 1G
innodb_flush_method = O_DIRECT

Restart MariaDB/MySQL for the changes to take effect.

Option 3: Adjust indexes

Indexes are essential for improving the per­for­mance of MySQL or MariaDB queries. Instead of scanning every row in a table, the database can use an index to jump directly to the relevant entries.

However, adding too many or in­ap­pro­pri­ate indexes can be coun­ter­pro­duc­tive, as each ad­di­tion­al index consumes storage space and slows down write op­er­a­tions. Therefore, it’s important to create indexes only on columns that are fre­quent­ly queried.

For example, if you have a table called users and often search by the email column, adding an index can sig­nif­i­cant­ly speed up these queries:

CREATE INDEX idx_user_email ON users(email);

With this index, queries like

SELECT * FROM users WHERE email='xyz@example.com';

are executed much faster, because the database doesn’t have to search through every row of the table, but instead can directly access the matching entries.

Ad­di­tion­al­ly, composite indexes can be useful when multiple columns are fre­quent­ly queried together. They allow the database to use a single index to ef­fi­cient­ly filter by several fields at once.

Indexes that are no longer needed or rarely used should also be removed regularly to free up storage space and improve write per­for­mance. In the following example, the index idx_old_column is deleted:

DROP INDEX idx_old_column ON users;

Option 4: Optimize queries

Complex or in­ef­fi­cient SQL queries can put a heavy load on the database and sig­nif­i­cant­ly reduce per­for­mance, es­pe­cial­ly when working with large tables. To optimize MySQL per­for­mance, first analyze how the database executes a query. The EXPLAIN command is par­tic­u­lar­ly useful for this.

EXPLAIN SELECT id, email FROM users WHERE status='active';

Using EXPLAIN, MySQL or MariaDB shows which indexes are used, how many rows need to be read, and in what order the tables are processed. This helps you assess whether a query is efficient or if further op­ti­miza­tions — such as adding indexes or adjusting joins — would be ben­e­fi­cial.

Avoid queries like SELECT *, as they retrieve all columns, including those not needed. Instead, ex­plic­it­ly select only the required columns. This reduces the amount of data trans­ferred and improves query speed. For complex joins, also make sure that the con­di­tions in the WHERE clause are as precise as possible to prevent un­nec­es­sary table scans.

Option 5: Set up repli­ca­tion and caching

Repli­ca­tion, which dis­trib­utes the load across multiple servers, and caching, which reduces the number of direct database accesses, can also help optimize the per­for­mance of MariaDB and MySQL.

Repli­ca­tion typically follows the master-slave principle: the master server handles all write op­er­a­tions, while one or more slave servers replicate the data and handle read queries. This setup allows the database to process high loads more ef­fi­cient­ly without over­load­ing the master server. Although con­fig­ur­ing repli­ca­tion requires some initial effort, it can greatly improve per­for­mance for heavily used ap­pli­ca­tions.

Caching can also sig­nif­i­cant­ly reduce response times. MySQL and MariaDB offer a Query Cache that stores the results of fre­quent­ly repeated queries so they don’t need to be re-executed. With the following settings, you can enable the query cache and define its size:

SET GLOBAL query_cache_size = 64*1024*1024;
SET GLOBAL query_cache_type = 1;

For modern ap­pli­ca­tions, it’s also ben­e­fi­cial to use external caching solutions like Redis, which can access fre­quent­ly needed data even faster.

Option 6: Use table par­ti­tion­ing

For very large tables, query pro­cess­ing can take longer because the database must scan every row. Par­ti­tion­ing allows you to split tables into smaller, logically separated parts — for example by date, ID range, or other criteria. Each partition is in­ter­nal­ly treated like a separate table, so queries that only target specific par­ti­tions can be executed much faster.

An example of par­ti­tion­ing an orders table by year might look like this:

CREATE TABLE orders (
id INT,
order_date DATE,
amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025)
);

In this case, all orders from 2023 will be stored in partition p2023, and all orders from 2024 in p2024.

Option 7: Use con­nec­tion pooling

Each new con­nec­tion to MySQL or MariaDB consumes time and resources. If your ap­pli­ca­tion opens and closes a con­nec­tion for every single request, it can create un­nec­es­sary load on the server. To avoid this, you can use con­nec­tion pooling to optimize MariaDB and MySQL. Con­nec­tion pooling keeps a set number of database con­nec­tions open per­ma­nent­ly, allowing ap­pli­ca­tions to reuse these existing con­nec­tions instead of creating new ones each time.

An example in PHP using mysqli could look like this:

$mysqli = new mysqli('localhost', 'user', 'password', 'db');
$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 5);

Instead of opening a new con­nec­tion for each request, the pool reuses existing con­nec­tions. This leads to faster response times while also reducing the load on the database server.