MySQL Performance Intermediate

Database optimization-speed up MySQL queries

Optimize MySQL and MariaDB performance: run OPTIMIZE TABLE, add indexes, tune my.cnf settings, and use query caching.

UnderHost NOC Network Operations

10 min read Updated June 2026

On this page

OPTIMIZE TABLE
Add missing indexes
MySQL configuration tuning
Query caching

Database optimization reduces query execution time and disk space. Start with OPTIMIZE TABLE to defragment, then add indexes for frequently-queried columns.

OPTIMIZE TABLE

After many INSERTs/DELETEs, tables become fragmented. OPTIMIZE reclaims space and sorts data:

OPTIMIZE TABLE wp_posts;
OPTIMIZE TABLE wp_options;
-- Or optimize all at once (via mysqlcheck):
mysqlcheck -u user -p --optimize --all-databases

Add missing indexes

Use EXPLAIN to find slow queries lacking indexes:

EXPLAIN SELECT * FROM posts WHERE status = 'published' AND user_id = 5;
-- If "key" column is NULL, add an index:
CREATE INDEX idx_status_user ON posts(status, user_id);

MySQL configuration tuning (VPS)

Key settings in /etc/mysql/my.cnf:

[mysqld]
innodb_buffer_pool_size = 256M   # 50-70% of RAM for InnoDB-heavy workloads
max_connections = 100             # Reduce if running out of memory
slow_query_log = 1                # Enable slow query logging
long_query_time = 1               # Log queries taking > 1 second