Why MySQL Picks index_author_id Over index_title: Execution & Cost Insights

Preface: MySQL Architecture

MySQL consists of a Server layer and a storage‑engine layer. The Server layer implements cross‑engine features such as connectors, query parser, optimizer, executor, stored procedures, triggers, views and a standardized binlog module. The storage‑engine layer handles actual data storage and retrieval and supports plug‑in engines like InnoDB, MyISAM, Memory, etc.

Experimental Environment

OS kernel: Tencent tlinux release 2.2 MySQL version: 5.7.10

Created table

tb_article

with two indexes

index_title

and

index_author_id

.

Inserted sample data.

SQL Execution Process

Analyzed the statement

select * from tb_article where author_id=20 and title='b';

using EXPLAIN and optimizer trace.

MySQL Client‑Server Communication

The client sends the SQL to the server using the MySQL protocol. Prior to 5.6 each connection created a dedicated thread (One‑Connection‑Per‑Thread). Since 5.6 a thread‑pool can be enabled. The protocol is half‑duplex.

Query Status

During a query the thread goes through many states, e.g.:

sleep : waiting for a new request.

query : executing the query or sending results.

locked : waiting for a table lock (storage‑engine row locks are not shown).

analyzing and statistics : collecting statistics and building the execution plan.

copying to tmp table : creating a temporary table for GROUP BY, ORDER BY or UNION.

sorting result : sorting the result set.

sending data : transmitting rows to the client.

Query Cache

The cache stores query results as key‑value pairs. If a cache miss occurs the query proceeds to the optimizer. In write‑heavy workloads the cache adds overhead; MySQL 8.0 removed the query cache.

Query Optimization Steps

The optimizer parses the SQL, performs preprocessing, and generates an execution plan. Errors at any stage abort the query.

Parser and Pre‑processor : builds a parse tree and validates syntax.

Optimizer : evaluates possible execution plans and chooses the cheapest one.

Execution Plan : represented as an instruction tree executed by the storage engine.

EXPLAIN Output Fields

Explanation of the ten columns: id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra. Details include meaning of each type (ALL, index, range, ref, eq_ref, const, system, NULL) and how MySQL estimates costs.

Cost Model

Total cost = CPU Cost + IO Cost. Server‑side costs include row_evaluate_cost, memory_temptable_create_cost, memory_temptable_row_cost, key_compare_cost, disk_temptable_create_cost, disk_temptable_row_cost. Engine‑side costs include io_block_read_cost and memory_block_read_cost. MySQL 5.7 stores these in

mysql.server_cost

and

mysql.engine_cost

tables.

Index Selection Example

Running EXPLAIN for the sample query shows that MySQL chooses

index_author_id

because its estimated cost is lower than

index_title

. Adding a composite index

index_title_author(title,author_id)

makes the cost equal; MySQL then prefers the index with fewer leaf blocks.

For the projection query

select title, author_id from tb_article where author_id=20 and title='b';

MySQL prefers the covering composite index

index_title_author

, avoiding a table lookup.