MySQL Slow Query and Index Optimization

MySQL Slow Query and Index Optimization

1. Overview

When optimizing MySQL, you usually need to analyze the database using tools such as EXPLAIN, slow‑query logs, SHOW commands, and profiling to locate performance bottlenecks and adjust internal configurations. Understanding slow‑query and index concepts helps QA engineers and developers diagnose and improve system stability.

2. Slow Queries – Using SHOW to Find System Bottlenecks

Example: querying a production replica configuration.

2.1 Query All Server Variables

SHOW VARIABLES

2.2 Query Slow‑Query Related Variables

SHOW VARIABLES LIKE '%slow%'

2.3 Query Slow‑Query Count

SHOW GLOBAL STATUS LIKE '%slow%'

The configuration enables slow‑query logging for queries longer than 2 seconds; the system currently shows 652 slow queries. Analyzing the slow‑query log helps identify inefficient SQL statements.

2.4 Connection‑Related Variables

1) Maximum connections:

SHOW VARIABLES LIKE 'max_connections'

The server allows up to 3000 connections. Adjust this value only if the machine can handle the additional file descriptors.

2) Current used connections:

SHOW GLOBAL STATUS LIKE 'Max_used_connections'

Only 12 connections have been used, which is 0.4 % of the maximum. Ideally this ratio should be around 85 %; a value below 10 % suggests the max‑connection setting is too high.

2.5 back_log

The back_log parameter defines how many connection requests can be queued when the server is busy. The current value is 256 (default 50, Linux max 512).

2.6 Cache Variables

1) Global index buffer: key_buffer_size key_buffer_size determines the size of the index buffer for MyISAM tables. The ratio key_reads / Key_read_requests should be around 0.1 % for optimal performance.

Example query:

SHOW VARIABLES LIKE 'key_buffer_size'

The server has allocated 32 MB to key_buffer_size. Checking usage with SHOW GLOBAL STATUS LIKE 'key_read%' shows 1 452 448 index read requests, only 14 of which missed the cache (miss rate ≈ 0.001 %). The recommended miss rate is ≤ 0.1 %.

3. Common Tools for Analyzing Slow‑Query Logs

3.1 mysqldumpslow

Use mysqldumpslow to summarize slow‑query logs, e.g.:

/path/mysqldumpslow -s c -t 10 /database/mysql/slow-query.log

– shows the 10 most frequent SQL statements.

Options: -s (sort by count, time, rows, etc.), -t (top N), -g (regex filter), -r (reverse order).

3.2 EXPLAIN Analysis

Running EXPLAIN SELECT ... reveals the read order, join type, possible and actual indexes, rows examined, and extra information such as Using where.

table – which table the row belongs to

type – join type (system, const, eq_ref, ref, range, index, ALL)

possible_keys – indexes that could be used

key – index actually used

key_len – length of the used index

rows – estimated rows examined

Extra – additional optimizer notes

Example output image:

3.3 profiling

Enable profiling with SET profiling = 1; and view details using SHOW PROFILE FOR QUERY 1;. This provides fine‑grained timing for each phase of query execution.

4. Index and Query Optimization

4.1 Types of Indexes

Normal index – no uniqueness constraint

Unique index (UNIQUE) – enforces uniqueness

Primary key – a special unique index

Full‑text index (FULLTEXT) – for VARCHAR/TEXT columns

MySQL stores most indexes in B‑tree structures; hash indexes are used for MEMORY tables.

4.2 Recommendations for Building Indexes

Follow the left‑most prefix rule

Prefer small, simple data types (e.g., integers)

Avoid NULL columns; use NOT NULL or substitute values

Do not create redundant or duplicate indexes

Ensure join columns have the same type and are indexed

Use prefix indexes for long strings (≤ 8 characters)

4.3 SQL Design Tips

Prefer IN over OR (limit to < 1000 values)

Avoid implicit type conversion; quote strings, not numbers

Prefer JOIN to sub‑queries when possible

Minimize arithmetic or function calls inside MySQL

Batch statements to reduce round‑trips

Split complex queries into smaller ones

Paginate large result sets (≤ 2000 rows, < 1 MB)

Use UNION ALL instead of UNION when duplicates are not a concern

Use COUNT(*) for row counts

Avoid SELECT *; list required columns

Avoid nondeterministic functions like NOW(), RAND(), SYSDATE(), CURRENT_USER() Always specify column list in INSERT Do not update multiple tables in a single statement

5. Basic Steps for Slow‑Query Optimization

Run the query with SQL_NO_CACHE to verify slowness.

Apply WHERE filters to the table that returns the fewest rows.

Use EXPLAIN to compare the execution plan with expectations.

Prefer ORDER BY ... LIMIT patterns that let MySQL stop early.

Understand the business use case.

When adding indexes, follow the recommendations above.

Iterate: if results are not satisfactory, return to step 1.

6. Case Study

Using a slow query from section 3.2, we run EXPLAIN and see that the query on table t_order uses the ref join type with the buyer_phone index, returning 3 rows.

When the buyer_phone column (VARCHAR(20)) is compared to a numeric literal, MySQL performs an implicit conversion, causing the index to be ignored and the query time jumps from ~0.003 s to ~1.5 s.

Reordering the WHERE clause to place the indexed column first reduces execution time to 0.001 s, a three‑fold improvement.

7. QA Checklist for SQL Review

Key points for QA when reviewing SQL statements:

Document the reason for any online data modification and verify syntax and business logic.

Avoid SELECT *; request specific columns.

Back up affected databases/tables before executing DELETE or DROP statements.

For UPDATE or ALTER, confirm business impact and have rollback scripts ready.

Compare any schema changes against the current production structure in a test environment first.

After execution, verify related system functionality and coordinate with developers if needed.

Re‑run the SQL in a test environment after deployment to ensure consistency.