Boost MySQL Performance: Config Tweaks, Index Strategies, and Essential Tools

MySQL remains the world’s most popular relational database, but default installations are rarely optimal; this guide explores practical optimization techniques and ties them to newer MySQL features.

Configuration Optimization

Even after MySQL 5.7 the default settings leave ample room for improvement.

Configuration File

On Linux the main file is /etc/mysql/my.cnf (or files such as /etc/mysql/mysql.conf.d/mysqld.conf). Copy it for safe editing, e.g.: cp /etc/mysql/my.cnf /home/vagrant/Code Edit with a familiar editor (vi, emacs, etc.) and replace the original after backing it up:

sudo vim /etc/mysql/my.cnf

Manual Tuning

Place the following directives in the [mysqld] section:

innodb_buffer_pool_size = 1G   # 50‑70% of RAM

innodb_log_file_size = 256M

innodb_flush_log_at_trx_commit = 1   # or 2/0

innodb_flush_method = O_DIRECT

Explanation:

innodb_buffer_pool_size caches data and indexes in memory; allocate 50‑70% of RAM for best performance.

innodb_log_file_size controls the size of the binary log; larger logs improve throughput but increase recovery time.

innodb_flush_method set to O_DIRECT to avoid double buffering, especially on SSD‑based cloud hosts.

MySQL Tuner

MySQL‑Tuner (Perl) can be run weekly to collect diagnostic data:

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

chmod +x mysqltuner.pl

./mysqltuner.pl

Sample output highlights InnoDB buffer pool size, log file ratios, and other key metrics.

Indexes

Indexes accelerate SELECT queries but add overhead on INSERT/UPDATE. Proper index design is crucial.

Unique / Primary Index

Ensures column values are unique, e.g.:

ALTER TABLE `users` ADD UNIQUE INDEX `username` (`username`);

Regular Index

Speeds up lookups without uniqueness constraints:

ALTER TABLE `users` ADD INDEX `usercountry` (`username`, `country`);

Full‑Text Index

Supported by InnoDB/MyISAM for CHAR, VARCHAR, TEXT columns, enabling keyword searches.

Descending Index (MySQL 8+)

Allows ordering results in descending order directly in the index:

CREATE TABLE t (
  c1 INT, c2 INT,
  INDEX idx1 (c1 ASC, c2 ASC),
  INDEX idx2 (c1 ASC, c2 DESC),
  INDEX idx3 (c1 DESC, c2 ASC),
  INDEX idx4 (c1 DESC, c2 DESC)
);

Helper Tool: EXPLAIN

Prefix a SELECT with EXPLAIN to see index usage and row estimates:

EXPLAIN SELECT City.Name FROM City JOIN Country ON (City.CountryCode = Country.Code) WHERE City.CountryCode = 'IND' AND Country.Continent = 'Asia';

Helper Tool: Percona Duplicate‑Key Checker

Detects unnecessary duplicate indexes, e.g. in a WordPress wp_posts table, and suggests removal:

ALTER TABLE `homestead`.`wp_posts` DROP INDEX `type_status_date`, ADD INDEX `type_status_date` (`post_type`,`post_status`,`post_date`);

Helper Tool: Percona Unused‑Index Finder

Run pt-index-usage /var/log/mysql/mysql-slow.log to locate indexes never used by slow queries.

Detect Bottlenecks

Enable slow‑query logging:

slow_query_log = /var/log/mysql/mysql-slow.log

long_query_time = 1

log-queries-not-using-indexes = 1

Analyze the log with Percona Toolkit:

pt-query-digest /var/log/mysql/mysql-slow.log

Summary

This comprehensive guide covered MySQL configuration tuning, key InnoDB parameters, various index types, and essential Percona tools for diagnosing duplicate or unused indexes and slow queries, providing a solid foundation for accelerating MySQL workloads.