Master MySQL Performance: Indexes, EXPLAIN Insights, and Slow Query Tactics

Introduction

This article gathers frequently used MySQL operations, highlights unreasonable practices, and presents useful information collected while optimizing slow queries. It is intended for developers with a basic understanding of MySQL.

1. Index Related

1.1 Index Cardinality

Cardinality is the number of distinct values in a column. High cardinality (few duplicate values) yields the best index performance, while low cardinality (e.g., gender column with only "M" and "F") provides little benefit. When a column’s distinct‑value percentage exceeds roughly 30%, the optimizer often ignores the index and performs a full‑table scan.

1.2 Reasons Indexes May Fail

Applying functions or arithmetic to indexed columns (e.g., +, -, *, /, !, <>, % or LIKE with a leading wildcard) prevents index usage.

Type mismatches, such as comparing a VARCHAR column with a numeric literal.

Using internal functions on indexed columns without a functional index (MySQL 8.0 supports functional indexes; in 5.7 you can use virtual columns).

OR conditions disable index usage unless each operand column has its own index.

String literals must be quoted; otherwise the index is ignored.

B‑tree indexes do not use IS NULL but do use IS NOT NULL; bitmap indexes behave similarly.

Composite indexes follow the left‑most prefix rule.

1.3 Index Creation Guidelines

Base indexes on the queries that are executed most often.

Prefer columns with high selectivity, calculated as COUNT(DISTINCT col) / COUNT(*).

Define unique keys for columns that must be unique; this both enforces data integrity and improves index efficiency.

2. Useful Information from EXPLAIN

2.1 Basic Usage

Run EXPLAIN SELECT … or DESC SELECT … to view the execution plan.

Use EXPLAIN EXTENDED followed by SHOW WARNINGS to see the exact statement the optimizer will execute.

2.2 Performance‑Enhancing Features

Covering Index : All required columns are present in the index, eliminating the need to read the table rows ( Using index in the EXTRA column).

Index Condition Pushdown (ICP) : From MySQL 5.6 onward, WHERE conditions that can be evaluated using the index are pushed down to the storage engine, shown as Using index condition.

Index Merge : Multiple indexes are scanned separately and their results are merged (INTERSECT/UNION). The EXPLAIN output shows index_merge and the specific merge algorithm.

2.3 EXTRA Column Details

Using filesort : MySQL performs an external sort rather than reading rows in index order.

Using temporary : A temporary table is created for GROUP BY or ORDER BY operations.

Using index : Indicates a covering index is used.

Impossible where : The WHERE clause can never be true.

Select tables optimized away : MIN/MAX or COUNT(*) on MyISAM can be resolved during the planning phase.

Distinct : Stops scanning after the first matching row is found.

2.4 TYPE Column Explanation

system : Table has only one row (special case of const).

const : Index lookup finds a single row, typically via primary or unique key.

eq_ref : Unique index scan; one row per index key.

ref : Non‑unique index scan; returns all rows matching a given value.

range : Index range scan using BETWEEN, <, >, IN, etc.

index : Full index scan (reads only the index tree).

all : Full table scan.

3. Field Types and Encoding

String Length Functions : CHARACTER_LENGTH() (or CHAR_LENGTH()) returns the number of characters; LENGTH() returns the number of bytes (e.g., a Chinese character occupies three bytes).

Index Length for VARCHAR : Estimate selectivity with SELECT COUNT(DISTINCT LEFT(col,5))/COUNT(*) FROM tbl; values closer to 1 indicate high selectivity.

UTF‑8 Limitations : MySQL’s utf8 charset stores up to three bytes per character and cannot represent emoji. Use utf8mb4 and set character_set_server=utf8mb4 and character_set_client=utf8mb4 in the configuration.

Collations : utf8_general_ci is case‑insensitive; utf8_general_cs (now deprecated) was case‑sensitive; utf8_bin stores strings in binary form and is fully case‑sensitive.

4. SQL Statement Summary

4.1 Frequently Forgotten Commands

INSERT IGNORE INTO …

– skips rows that would cause duplicate‑key errors. INSERT … ON DUPLICATE KEY UPDATE … – updates existing rows on conflict (affects auto‑increment). REPLACE INTO … – deletes the existing row (if any) and inserts the new one. CREATE TABLE backup AS SELECT * FROM original; – creates a backup copy. CREATE TABLE new LIKE old; – copies table structure only. INSERT INTO new SELECT * FROM old; – copies data via a SELECT. UPDATE a, b SET a.col=a.col+1 WHERE a.id=b.id; – multi‑table update. DELETE a FROM a, b WHERE a.id=b.id; – multi‑table delete.

4.2 Lock Commands (Reference Only)

SELECT … LOCK IN SHARE MODE;

– shared lock. SELECT … FOR UPDATE; – exclusive lock.

4.3 Optimization Hints

SELECT * FROM tbl FORCE INDEX(idx) …

– forces use of a specific index. SELECT * FROM tbl IGNORE INDEX(idx) … – prevents use of a specific index. SELECT … SQL_NO_CACHE … – disables query cache for testing.

4.4 Viewing Server Status

SHOW VARIABLES LIKE 'character_set%';

– displays character set settings. SHOW VARIABLES LIKE 'collation%'; – displays collation settings.

4.5 SQL Writing Tips

Place filtering conditions in WHERE rather than HAVING.

Use deferred join techniques for large pagination (e.g., LIMIT 10000,10).

Avoid excessive use of DISTINCT; prefer GROUP BY when possible.

Limit the number of tables in a join to three for better performance.

5. Common Pitfalls

TRUNCATE resets auto‑increment counters to zero.

Aggregate functions return NULL when all input rows are NULL (e.g., SUM(col)).

Never compare to NULL with =; use IS NULL or IS NOT NULL instead.

6. Online Schema Change for Massive Tables

When altering large tables, use Percona Toolkit’s pt-online-schema-change to avoid locking the table:

pt-online-schema-change --user='root' --host='localhost' --ask-pass \
  --alter "add index idx_user_id(room_id,create_time)" \
  D=fission_show_room_v2,t=room_favorite_info --execute

7. Slow Query Log

Locate the slow‑query log file and analyze it with mysqldumpslow. Common options: -t: limit output rows (e.g., top 10). -s: sort by a metric (default is average query time; c for count, t for time). -v: show full query text.

Example:

mysqldumpslow -v -s t -t 10 /path/to/mysql_slow.log

8. Viewing and Killing SQL Processes

Use the process list to monitor long‑running queries and terminate them if necessary: SHOW PROCESSLIST; To kill a specific thread: KILL 183665; GUI tools such as SQLyog also provide a visual process list with right‑click kill functionality.

9. Additional Performance Thoughts

Avoid application code that loops over a list of IDs and queries each individually; batch the IDs in a single IN clause or use JOINs.

Heavy ranking or aggregation queries on large tables may require off‑loading to a replica or caching results in Redis.

Very large OFFSET pagination (e.g., LIMIT 40000,1000) can be costly; consider keyset pagination or validate request limits to prevent abuse.

By applying the above practices, developers can significantly improve MySQL query performance and reduce the likelihood of encountering slow‑query issues.