MySQL Performance Tuning: Indexes, Slow Queries, and Key Parameters

A practical path from adding indexes to reading query plans, catching slow SQL, and tuning key parameters against real load.

A slow database is usually not a slow machine. More often the query takes the wrong path or a parameter sits at the wrong setting. Using MySQL 8.0 on Ubuntu/Debian as the example, this article walks you through indexes, query plans, the slow query log, and a few key parameters. Validate everything on your own server under real load, and never copy a "best config" from the internet verbatim.

Get the indexes right first

An index is the most direct way to speed up a query. The rule is simple: only columns that show up often in WHERE, JOIN, and ORDER BY are worth indexing.

-- Single-column index: look up orders by user
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- Composite index: follows the "leftmost prefix" rule, so column order must match the query
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

The composite index (userid, status) serves both WHERE userid = ? and WHERE userid = ? AND status = ?, but it cannot help a query that filters on status alone. More indexes are not always better: each one slows down writes, consumes disk, and can push the optimizer toward a worse plan.

Common pitfalls

  • Wrapping an indexed column in a function or expression kills the index: WHERE DATE(createdat) = '2026-07-14' skips it. Rewrite it as a range: WHERE createdat >= '2026-07-14' AND createdat < '2026-07-15'.
  • Implicit type conversion has the same effect: phone is a string but you write WHERE phone = 138xxxx (a number).
  • A leading wildcard, LIKE '%abc', cannot use an index.

Read the plan with EXPLAIN

Before you touch an index, let MySQL tell you how it intends to run the query:

EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = 'paid';

Focus on these columns:

  • type: ALL means a full table scan (the biggest red flag), ref/range are usually fine, and const/eqref are best.
  • key: the index actually chosen; NULL means no index was used.
  • rows: the estimated rows scanned — smaller is better.
  • Extra: Using filesort or Using temporary often signals expensive sorting or grouping.

To see real execution time, use EXPLAIN ANALYZE (it actually runs the statement).

Turn on the slow query log to find slow SQL

If you don't know which statement is slow, have MySQL record it. You can enable it live, without a restart:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;      -- log anything over 1 second
SET GLOBAL log_queries_not_using_indexes = 'ON';

To make it persistent, add it to /etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

Aggregate the log by time with mysqldumpslow -s t /var/log/mysql/slow.log to spot the worst offenders at a glance. Note that logqueriesnotusingindexes can flood the log on a busy server, so turn it off once you're done investigating.

A few key parameters

Don't memorize a template — understand what each knob does, then size it to your memory and connection count:

  • innodbbufferpoolsize: InnoDB's cache for data and indexes, and the single most important setting. On a dedicated database host, 50%–70% of physical RAM is reasonable; if the box is shared with your application, leave generous headroom so you don't drive it into OOM.
  • maxconnections: the ceiling on concurrent connections, 151 by default. Before raising it, confirm your memory can absorb it — every connection costs RAM, and a reckless bump makes an overload easier, not harder. A connection pool usually beats piling on more connections.
[mysqld]
innodb_buffer_pool_size = 4G
max_connections = 200

After editing, restart with sudo systemctl restart mysql, then watch the real effect through SHOW GLOBAL STATUS and the slow log.

Summary

The right order is: use EXPLAIN and the slow query log to find the bottleneck → add the right indexes for hot queries and eliminate full table scans → and only then fine-tune parameters against your real memory and concurrency. Verify each step with actual data from your own server. Measure first, tune second, and copy no "universal config" blindly.