How to Design the Optimal Index for a 27‑Column Table Query (Alibaba Interview)

Problem Statement

A table has 27 columns (A–Z) with id as the primary key. The interview asks for the most efficient index for the query SELECT a, b, c FROM table WHERE d = X ORDER BY e.

Optimal Index Design

The theoretically optimal index is a composite covering index (d, e, a, b, c). The ordering follows three principles:

Fast filtering : d is the equality predicate, placed first to enable quick range scans.

Avoid sorting : e follows d so the index rows are already ordered, eliminating Filesort.

Covering query : Including a, b, c makes the index cover all selected columns, removing the need for a table‑row lookup (back‑table).

Why This Order Works

With WHERE d = X the engine can locate the matching index range using the leftmost prefix d. Because e is next, the rows within that range are stored in e order, satisfying the ORDER BY without extra sorting. Finally, a, b, c are retrieved directly from the index.

Comparison with Other Indexes

Alternative indexes such as (d, e), (e, d), or single‑column indexes either fail to cover the query (requiring a back‑table) or cannot avoid sorting, leading to higher I/O and CPU costs. A two‑index scheme like (d, a, b, c) + (e) still needs a back‑table for e and an explicit Filesort, making it inferior to the single covering index.

Index Condition Pushdown (ICP)

ICP pushes applicable WHERE conditions down to the storage engine during index scanning, reducing rows sent to the server layer. It is triggered when a composite index contains non‑leftmost columns used in the predicate. In the optimal covering index, ICP is unnecessary because the query is fully satisfied by the index.

ICP Example

EXPLAIN SELECT a,b,c FROM table WHERE d = X ORDER BY e;

With index (d, e) (non‑covering), the engine applies d = X and can also filter on e during the scan, showing Using index condition in the plan. With the covering index (d, e, a, b, c), the plan shows Using index and no ICP flag.

MySQL Query Execution Process

The execution flow consists of five layers:

Connection Management : Authentication, thread allocation, and session variables.

Parsing : Lexical and syntactic analysis builds a parse tree.

Optimization : The optimizer evaluates cost models, chooses the best index, and generates the execution plan.

Execution : The executor invokes the storage engine; InnoDB performs index scans, optional ICP, and may sort in sort_buffer if needed.

Result Return : The executor packages rows and sends them to the client.

Server vs. InnoDB Responsibilities

Server layer : Connection handling, SQL parsing, optimizer decisions, execution scheduling, built‑in functions, and non‑covering sorts ( Filesort).

InnoDB layer : Physical data access, index scans, buffer‑pool management, transaction logging, and ICP filtering.

Practical Recommendations

Prefer a covering index that satisfies WHERE, ORDER BY, and SELECT columns.

Follow the “three‑star” index standard: high‑selectivity leftmost column, order‑matching column, and include all output columns.

Use EXPLAIN to verify Using index (covering) and ensure Using filesort is absent.

If a covering index is impossible, enable ICP by including the filtered column in the composite index.

Key Takeaway

The composite index (d, e, a, b, c) (or (d, e) INCLUDE (a, b, c) on engines that support INCLUDE) delivers the lowest possible cost for the given query by eliminating both back‑table lookups and explicit sorting.