How to optimize MySQL performance

A slow database slows down the whole site. A few simple tweaks make a big difference for MySQL.

1. The InnoDB buffer pool

The most important setting: give the buffer pool about 60-70% of the server RAM (if the server is dedicated to the database). In /etc/mysql/mysql.conf.d/mysqld.cnf:

innodb_buffer_pool_size = 2G

2. Find slow queries

Enable the slow query log:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

3. Add indexes

The most common cause of slowness is missing indexes on columns used in WHERE and JOIN. Analyze with:

EXPLAIN SELECT * FROM orders WHERE user_id = 42;

If you see a full table scan, add an index on that column.

4. RAM matters

MySQL benefits enormously from RAM, which keeps frequent data in memory. If your database grows, move to a plan with more RAM; you can resize without reinstalling.

5. Keep the database clean

Periodically run OPTIMIZE TABLE on large tables and delete data you no longer need.