Why 70% of System Outages Stem from SQL Performance: A MySQL Index Optimization Guide

"70%" is not a reproducible production statistic; the article treats it as a reminder that indexes often expose SQL problems, but CPU, locks, I/O, connection pools, and application retries can also be root causes. All examples use MySQL 8.0 and InnoDB with placeholders like <database host>, <database name>, and <business table>.

1. Identify where the slowness originates – Do not blame time‑outs in application logs on indexes alone. First examine the database load, currently running statements, lock waits, and the slow‑query summary within the failure window.

SELECT VERSION(), @version_comment, @sql_mode;

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

mysql --host='<database host>' --user='<readonly user>' --password \
  --database='<database name>' --execute='SHOW FULL PROCESSLIST';

SELECT DIGEST_TEXT, COUNT_STAR,
       ROUND(SUM_TIMER_WAIT/1000000000000,2) AS total_seconds,
       ROUND(AVG_TIMER_WAIT/1000000000,2) AS avg_ms,
       SUM_ROWS_EXAMINED, SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

SELECT w.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx,
       w.BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx,
       dl.OBJECT_SCHEMA, dl.OBJECT_NAME, dl.LOCK_TYPE, dl.LOCK_MODE
FROM performance_schema.data_lock_waits AS w
JOIN performance_schema.data_locks AS dl
  ON dl.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID;

SHOW ENGINE INNODB STATUS;

pidof mysqld
pidstat -p '<mysqld PID>' -rud 1 10
iostat -xz 1 10

# PromQL examples (use actual exporter metric names)
rate(mysql_global_status_questions[5m])
rate(mysql_global_status_innodb_row_lock_time[5m])

Lock waits, long transactions, or disk saturation are independent root causes; adding an index cannot replace shortening transactions, unifying update order, expanding capacity, or fixing retry storms. All outputs should retain timestamps and sources; example outputs are not production facts.

2. Prove the access path with execution plans – EXPLAIN estimates differ from real runtime. MySQL 8.0.18+ offers EXPLAIN ANALYZE, which actually runs the query; always validate the cost in a sanitized pre‑release environment before production use.

EXPLAIN FORMAT=TR\_E
SELECT id, created_at, status
FROM <business table>
WHERE tenant_id = <tenant ID>
  AND status = '<status>'
  AND created_at >= '<start time>'
ORDER BY created_at DESC
LIMIT 100;

EXPLAIN ANALYZE
SELECT id, created_at, status
FROM <business table>
WHERE tenant_id = <tenant ID>
  AND status = '<status>'
  AND created_at >= '<start time>'
ORDER BY created_at DESC
LIMIT 100;

3. Use ANALYZE TABLE wisely – It consumes resources; run it in low‑traffic windows and combine with monitoring. For small tables a full scan may be appropriate, but focus on actual rows examined, filter ratio, temporary tables, filesort cost, and execution time rather than merely checking type=ALL. ANALYZE TABLE <business table>; Applying functions to indexed columns usually prevents index usage. Rewrite conditions to enable range scans, e.g. replace WHERE DATE(created_at) = '<date>' with a range on created_at:

SELECT id FROM <business table>
WHERE created_at >= '<date> 00:00:00'
  AND created_at < '<next date> 00:00:00';

When a column is VARCHAR, comparing it to a numeric literal may trigger implicit conversion; use a quoted literal instead.

SELECT id FROM <business table> WHERE order_no = 123456;

SELECT id FROM <business table> WHERE order_no = '<order number>';

4. Design composite indexes around real query patterns – Typically place equality‑filtered columns first, followed by range and sort columns. Verify that the index also satisfies ordering with EXPLAIN. Low‑cardinality columns rarely need separate indexes, and the belief that “any column first is faster” is unsupported without evidence.

5. Build indexes in a rollback‑safe manner – Large‑table DDL can wait for metadata locks and affect replication. First back up the table structure, check for long transactions, confirm MySQL version and change window. High‑risk tables should use an online schema‑change platform or low‑peak gray‑scale rollout instead of direct execution during peak load.

#!/usr/bin/env bash
set -euo pipefail
DB_HOST='<database host>'
DB_NAME='<database name>'
TABLE_NAME='<business table>'
mysqldump --host="$DB_HOST" --user='<backup user>' --password \
  --no-data --routines --events "$DB_NAME" "$TABLE_NAME" \
  > "${TABLE_NAME}-schema-$(date -u +%Y%m%dT%H%M%SZ).sql"

This dump contains only the schema; it does not guarantee data recoverability. Also verify backup usability, binlog retention, and replica status.

SELECT trx_id, trx_started, trx_mysql_thread_id,
       trx_rows_locked, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

Creating the index:

CREATE INDEX idx_orders_tenant_status_created
ON <business table> (tenant_id, status, created_at DESC);

Online DDL syntax (MySQL 8+):

ALTER TABLE <business table>
ADD INDEX idx_orders_tenant_status_created (tenant_id, status, created_at DESC),
ALGORITHM=INPLACE,
LOCK=NONE;

Different MySQL versions, table structures, and index types support ALGORITHM and LOCK differently. If the requested online method fails, do not silently fall back to a high‑lock approach; first consult version‑specific documentation or the change‑platform solution. During execution, continuously monitor metadata locks, disk usage, replication lag, and application errors.

EXPLAIN ANALYZE
SELECT id, created_at, status
FROM <business table>
WHERE tenant_id = <tenant ID>
  AND status = '<status>'
  AND created_at >= '<start time>'
ORDER BY created_at DESC
LIMIT 100;

Inspect index cardinality:

SELECT TABLE_NAME, INDEX_NAME, SUM(CARDINALITY) AS cardinality_sum
FROM information_schema.statistics
WHERE table_schema = '<database name>'
  AND table_name = '<business table>'
GROUP BY TABLE_NAME, INDEX_NAME;

Dropping an index is equally risky; ensure the index was created by the current change, has no dependent production queries, and verify replica lag and maintenance window before rollback.

DROP INDEX idx_orders_tenant_status_created ON <business table>;

6. Two common pagination patterns – Deep offset scans discard many rows; for stable‑sorted lists, keyset pagination is more controllable. The ordering key must be unique or combined with the primary key to avoid missing or duplicate rows.

-- Deep offset (inefficient)
SELECT id, created_at
FROM <business table>
WHERE tenant_id = <tenant ID>
ORDER BY created_at DESC
LIMIT 100 OFFSET 100000;

-- Keyset pagination (efficient)
SELECT id, created_at
FROM <business table>
WHERE tenant_id = <tenant ID>
  AND (created_at < '<previous page time>'
       OR (created_at = '<previous page time>' AND id < <previous page ID>))
ORDER BY created_at DESC, id DESC
LIMIT 100;

Index to support keyset pagination:

CREATE INDEX idx_orders_tenant_created_id
ON <business table> (tenant_id, created_at DESC, id DESC);

The N+1 problem often manifests as high QPS while individual SQL statements are not slow. Use tracing to count per‑request SQL statements, then apply controlled batch queries or pre‑loading to reduce round‑trips. The size of IN lists must respect packet size, lock‑hold time, and application memory limits.

SELECT id, customer_id, status
FROM <business table>
WHERE id IN (<ID list>);

7. Acceptance criteria – Successful optimization must be demonstrated with matching‑load metrics: rows examined, latency percentiles, database CPU/I/O, lock wait time, replication lag, and error rate. A single cached‑plan EXPLAIN is insufficient.

# Verify runtime metrics
mysql --host='<database host>' --user='<readonly user>' --password \
  --execute='SHOW GLOBAL STATUS LIKE "Threads_running";'
mysql --host='<database host>' --user='<readonly user>' --password \
  --execute='SHOW GLOBAL STATUS LIKE "Slow_queries";'

SHOW CREATE TABLE <business table>;
SHOW INDEX FROM <business table>;

Retain the SQL summary, time window, original and new plans, gray‑scale scope, change duration, metrics, and rollback conditions. Only logs, metrics, lock evidence, or execution plans can substantiate the root‑cause conclusion; “add more indexes and try” is not a valid optimization strategy.