Understanding MySQL Query Execution, Indexes, Slow Queries and Optimization Practices

MySQL consists of a Server layer (connector, query cache, parser, optimizer, executor, built‑in functions) and a storage‑engine layer that handles data storage and retrieval.

The execution of a SQL query follows these stages: the connector establishes the client connection, the query cache may return a cached result, the parser performs lexical and syntactic analysis, the optimizer chooses the best execution plan and indexes, and finally the executor interacts with the storage engine to fetch and return the result.

Indexes act as a "table of contents" for data; InnoDB stores rows in a clustered primary‑key B+‑tree and secondary indexes in separate B+‑trees. Primary‑key indexes store the full row, while secondary indexes store only the primary‑key values, requiring a lookup (the “back‑table” operation) when queried.

A slow query is any SQL statement that takes excessive time to execute, often caused by missing indexes, leading to performance degradation or even MySQL crashes. Detecting slow queries can be done indirectly via application latency monitoring or directly by enabling and monitoring MySQL’s slow‑query log.

Common slow‑query scenarios include: (1) no index used on large tables, (2) optimizer choosing an unexpected index. Solutions involve creating appropriate indexes, forcing the optimizer to use a specific index with FORCE INDEX , rewriting queries to guide index selection, or redesigning the schema (e.g., partitioning large tables).

Two practical optimization examples are presented:

1) Adding a secondary index on script_id to a log table reduced execution time from 1.8 s to near‑zero, changing the EXPLAIN type from ALL (full scan) to ref (index lookup).

2) When the optimizer chose a sub‑optimal index, using FORCE INDEX(a) or rewriting the ORDER BY clause (e.g., ORDER BY b,a LIMIT 1 ) forced the use of a more efficient index, cutting execution time from 0.06 s to 0.01 s.

Additional MySQL pitfalls include GROUP_CONCAT truncation (default max length 1024) and data‑type overflow (e.g., INT exceeding its range). Recommendations are to increase group_concat_max_len , use larger data types like BIGINT, apply unsigned modifiers where appropriate, and validate input values in application code.