Optimize from evidence, not guesses
Query optimization is a disciplined process of finding a meaningful performance problem and changing the smallest relevant part. First determine which query matters: capture latency distribution, call frequency, rows examined, result size, and workload context. A query taking 200 milliseconds once a day may matter less than one taking 30 milliseconds thousands of times per second. Agree on a measurable goal such as reducing tail latency or server work without increasing write cost unacceptably.
Reproduce with the actual SQL, parameter types, schema, indexes, and representative data distribution. Development data is often too small or uniform to reveal a scan, skew, or lock issue. A query can be slow because MySQL is reading too many rows, sorting or grouping a large result, waiting for another transaction, transferring unnecessary columns, or because the application processes results inefficiently. Diagnose the layer before changing an index.
Inspect plans and predicates
Use EXPLAIN to see the optimizer's expected access paths, join order, and row estimates. Look for an unexpected large scan, poor selectivity, or avoidable sort. A scan is not inherently wrong; a query returning most rows may be cheaper to scan. Where supported and safe, EXPLAIN ANALYZE provides actual execution metrics but runs the statement, so avoid casually applying it to writes or heavy production queries. Compare actual behavior and estimates, not just plan labels.
Write predicates that allow MySQL to use the intended access path. For example, a date range on an indexed timestamp column may be more index-friendly than wrapping the column in DATE for every row. Use compatible parameter types and avoid implicit conversion of indexed values. A leading-wildcard LIKE search usually cannot seek by a B-tree prefix. These are practical tendencies, not substitutes for observing the plan; generated or functional indexes may be valid alternatives in supported versions.
-- Prefer a range predicate for a timestamp interval
SELECT order_id, total
FROM orders
WHERE created_at >= '2026-01-01'
AND created_at < '2026-02-01';Design indexes for a workload
Indexes can accelerate filters, joins, and ordering, but every index has storage and write-maintenance cost. Design composite indexes around actual query shapes and column order. Consider how equality predicates, ranges, and ORDER BY interact; a composite index's leftmost columns matter. Check for an existing index that already serves the need before adding another. A covering index may reduce table lookups but can become wide and expensive. Review index types and trade-offs.
Do not add an index to every column in WHERE. A low-selectivity column or a query that returns a large fraction of a table may not benefit. Measure read improvements alongside insert/update latency, storage, and replication effects. Test online DDL options and lock impact for the target version and operation, especially on large tables.
Reduce unnecessary work
Select only the columns needed by the caller and constrain result size. Returning a large document or every row can dominate network and application costs even if the lookup is indexed. Use deterministic pagination for user-facing lists, and consider keyset pagination for deep sequential navigation. For joins, verify cardinality and aggregate at the right level to avoid multiplying rows. For reports, filter source rows before grouping when logically correct and avoid redundant DISTINCT that masks a relationship error.
Long-running transactions, lock waits, and overloaded connection pools can appear as slow queries. Inspect transaction age, lock contention, and connection wait time alongside EXPLAIN. Keep transactions short, index write predicates where useful, and set timeouts with an explicit failure strategy. Increasing connection limits may worsen saturation rather than improve throughput.
A reliable tuning loop
Record a baseline, change one variable, and rerun the same representative test. Confirm the result set remains correct, then monitor in a staging environment or a controlled rollout. Compare latency percentiles, CPU, rows examined, lock waits, buffer usage, and write behavior. A plan that improves one parameter value can regress another due to data skew. Test a representative set of values and workloads before declaring success.
Avoid premature denormalization, hints, or server configuration changes without evidence. Query hints can freeze assumptions that become wrong as data changes; caching introduces invalidation and freshness problems. Use the simplest change that meets the measured goal and document why the index or rewrite exists so future maintainers can reassess it.
Performance tuning is an iterative engineering discipline, not a bag of universal tricks. Measure the bottleneck, inspect the plan, make a focused change, and check both correctness and whole-workload impact. Continue with EXPLAIN, indexes, and InnoDB behavior.