How to optimize MySQL and MariaDB query performance

Query performance determines whether MySQL and MariaDB stay responsive under load or spiral into timeouts. Efficient queries reduce CPU burn, shrink lock hold times, and cut disk I/O, keeping latency predictable as tables and concurrency grow.

The SQL optimizer chooses an execution plan by weighing indexes, column statistics, join strategies, and cost estimates. The slow query log supplies real-world candidates for tuning, and EXPLAIN shows how each statement executes—index lookups versus scans, plus indicators such as temporary tables or filesorts.

Most improvements involve trade-offs: extra indexes speed reads while increasing write cost, logging adds overhead plus storage churn, and table maintenance can disrupt production workloads. Schema changes and configuration changes benefit from a baseline, controlled rollout, and a rollback path when plans regress or DDL runs longer than expected.

MySQL/MariaDB query optimization checklist:

Verify slow query logging settings for representative capture.

$ mysql --table -u root -p -e "SHOW VARIABLES WHERE Variable_name IN ('slow_query_log','slow_query_log_file','long_query_time','log_queries_not_using_indexes');"
+-------------------------------+-------------------------------+
| Variable_name                 | Value                         |
+-------------------------------+-------------------------------+
| log_queries_not_using_indexes | OFF                           |
| long_query_time               | 1.000000                      |
| slow_query_log                | ON                            |
| slow_query_log_file           | /var/log/mysql/mysql-slow.log |
+-------------------------------+-------------------------------+

slow_query_log must be ON to collect slow statements.

long_query_time is in seconds. Lowering it increases log volume.

log_queries_not_using_indexes is useful for short captures. It can be noisy on OLTP workloads.

Summarize the slow query log to select tuning targets.
```
$ sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log
Count: 2  Time=1.36s (2s)  Lock=0.00s (0s)  Rows_sent=1.0 (2), Rows_examined=0.0 (0), Rows_affected=0.0 (0), root[root]@localhost
  SELECT SLEEP(N.N)
```
Sort by total time (t) or call count (c) to find the biggest wins.

MariaDB packages may provide mariadb-dumpslow as an alias for mysqldumpslow.

Use the log path from slow_query_log_file. Common locations include /var/log/mysql/mysql-slow.log.

Related: How to analyze MySQL or MariaDB slow query log

Run EXPLAIN for a top slow statement to identify plan bottlenecks.

$ mysql -u root -p -e "EXPLAIN SELECT id, total FROM appdb.orders_perf WHERE customer_id = 1 ORDER BY created_at DESC LIMIT 3\G"
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders_perf
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 8
     filtered: 12.50
        Extra: Using where; Using filesort

Red flags include type: ALL, Using filesort, Using temporary, or a high rows estimate.

Predicates that wrap an indexed column in a function often prevent index use.

Create a composite index that matches the query predicate plus sort order.
```
$ mysql -u root -p -e "CREATE INDEX idx_orders_perf_customer_created ON appdb.orders_perf (customer_id, created_at);"
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
```
Building indexes increases write overhead plus disk usage. Long-running DDL can hold metadata locks that stall writes.

Composite index order typically follows the most selective equality filters first, then range filters, then ordering columns.

Related: How to create an index in MySQL or MariaDB

Refresh optimizer statistics after large data churn.

$ mysql --table -u root -p -e "ANALYZE TABLE appdb.orders_perf;"
+-------------------+---------+----------+----------+
| Table             | Op      | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| appdb.orders_perf | analyze | status   | OK       |
+-------------------+---------+----------+----------+

Run ANALYZE TABLE after bulk loads or large deletes to refresh statistics.

Re-run EXPLAIN to confirm index usage for the tuned statement.

$ mysql -u root -p -e "EXPLAIN SELECT id, total FROM appdb.orders_perf WHERE customer_id = 1 ORDER BY created_at DESC LIMIT 3\G"
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders_perf
   partitions: NULL
         type: ref
possible_keys: idx_orders_perf_customer_created
          key: idx_orders_perf_customer_created
      key_len: 4
          ref: const
         rows: 5
     filtered: 100.00
        Extra: Backward index scan

Expect key to match the intended index plus reduced rows. Plan regressions often show up as scans or filesorts.

Use OPTIMIZE TABLE only when reclaiming space or addressing fragmentation is required.

$ mysql --table -u root -p -e "OPTIMIZE TABLE appdb.orders_perf;"
+-------------------+----------+----------+-------------------------------------------------------------------+
| Table             | Op       | Msg_type | Msg_text                                                          |
+-------------------+----------+----------+-------------------------------------------------------------------+
| appdb.orders_perf | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| appdb.orders_perf | optimize | status   | OK                                                                |
+-------------------+----------+----------+-------------------------------------------------------------------+

OPTIMIZE TABLE can rebuild the table. On large tables it can run for hours or block depending on engine and version.

Validate improvements with a post-change slow-log summary plus workload metrics.

$ sudo mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log
Count: 2  Time=1.36s (2s)  Lock=0.00s (0s)  Rows_sent=1.0 (2), Rows_examined=0.0 (0), Rows_affected=0.0 (0), root[root]@localhost
  SELECT SLEEP(N.N)

$ mysql --table -u root -p -e "SHOW GLOBAL STATUS LIKE 'Slow_queries';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 4     |
+---------------+-------+

Compare the same workload window before and after changes. Track latency percentiles, CPU, buffer pool reads, lock waits.

Author: Mohd Shakir Zakaria
Mohd Shakir Zakaria is a cloud architect with deep roots in software development and open-source advocacy. Certified in AWS, Red Hat, VMware, ITIL, and Linux, he specializes in designing and managing robust cloud and on-premises infrastructures.