MySQL Index Optimization: Confirm Slow Queries, Prove Access Paths, Add Indexes Safely

Confirm Where the Slowness Is

Don't blame indexes directly for application timeouts; first examine database load, executing statements, lock waits, and slow query summary during the fault window.

SELECT VERSION(), @@version_comment, @@sql_mode;
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

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;

mysql --host='<database_host>' --user='<readonly_user>' --password \
  --database='<database_name>' --execute='SHOW FULL PROCESSLIST'

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

# Metric names depend on the actual mysqld exporter
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 indexes cannot replace shortening transactions, unifying update order, expanding capacity, or correcting retry storms. All outputs must be saved with timestamps and collection sources; example outputs are not production facts.

Prove Access Paths with Execution Plans

EXPLAIN estimates do not equal actual latency; MySQL 8.0.18+ EXPLAIN ANALYZE actually executes the SQL. Confirm query cost before production use, preferably reproducing in a sanitized staging environment.

EXPLAIN FORMAT=TREE
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;

ANALYZE TABLE <business_table>;

ANALYZE TABLE consumes resources; run during low traffic with monitoring. Small-table full scans may be correct; focus on actual rows examined, filter ratio, temporary tables, filesort, sort cost, and execution time—not just type=ALL.

SELECT COUNT(*) AS total_rows,
       COUNT(DISTINCT tenant_id) AS tenant_cardinality,
       COUNT(DISTINCT status) AS status_cardinality
FROM <business_table>;

-- Calling a function on a column usually prevents index use
SELECT id FROM <business_table>
WHERE DATE(created_at) = '<date>';

-- Rewrite as a range condition that can use an index
SELECT id FROM <business_table>
WHERE created_at >= '<date> 00:00:00'
  AND created_at < '<next_date> 00:00:00';

-- If order_no is varchar, a numeric literal may trigger implicit conversion
SELECT id FROM <business_table>
WHERE order_no = 123456;

-- Use a string literal instead
SELECT id FROM <business_table>
WHERE order_no = '<order_no>';

Composite indexes must be designed around real query combinations. Common practice: equality columns first, then range and sort columns, but whether sorting can be satisfied must be verified with actual EXPLAIN. Low-cardinality columns should not be indexed alone, and no rule says "any column placed first is faster."

Create Indexes in a Rollback-Safe Way

Large-table DDL may wait for metadata locks and affect replication. Back up schema, check for long transactions, verify version and change window; high-risk tables must use the team's online schema change platform or off-peak canary—not peak-hour direct execution.

#!/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 is only a structure backup, not a data recovery backup. Also confirm 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;

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

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 explicitly requested online method fails, do not silently fall back to a high-lock method; first check version documentation and the change platform. During execution, continuously monitor metadata locks, disk, 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;

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;

DROP INDEX idx_orders_tenant_status_created ON <business_table>;

DROP INDEX is equally high-risk. Before rollback, confirm the index was created by this change, is not depended on by other live queries, and check replica lag and maintenance window.

Two Common Access Patterns

Deep pagination scans and discards many rows. For stable-sort lists, keyset pagination is often more controllable; the sort key must be unique or include the primary key as a second sort key to avoid missing or duplicate rows.

-- Deep pagination with OFFSET
SELECT id, created_at
FROM <business_table>
WHERE tenant_id = <tenant_id>
ORDER BY created_at DESC
LIMIT 100 OFFSET 100000;

-- Keyset pagination using created_at and id
SELECT id, created_at
FROM <business_table>
WHERE tenant_id = <tenant_id>
  AND (created_at < '<prev_page_time>'
       OR (created_at = '<prev_page_time>' AND id < <prev_page_id>))
ORDER BY created_at DESC, id DESC
LIMIT 100;

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

N+1 often manifests as high QPS while individual SQL may not be slow. After tracing to count SQL statements per request, use controlled batch queries or preloading to reduce round trips; IN list size must respect packet size, lock hold time, and application memory constraints.

SELECT id, customer_id, status
FROM <business_table>
WHERE id IN (<id_list>);

SELECT o.id, o.status, c.name
FROM <business_table> AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.tenant_id = <tenant_id>
  AND o.created_at >= '<start_time>';

Verification

Optimization success requires concurrent evidence under equivalent load: rows examined, latency percentiles, database CPU/I/O, lock waits, replication lag, and error rates—not just a single cache-hit EXPLAIN.

#!/usr/bin/env bash
set -euo pipefail
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 problem SQL digest, time window, old and new plans, canary scope, change duration, metrics, and rollback criteria. Only logs, metrics, lock evidence, or execution plans can support root-cause conclusions; "add more indexes and see" is not an optimization method.