Why SELECT * Slows Down MySQL: Deep Dive into Indexes and Optimization

In interviews you often hear "don't use SELECT *" but few understand why it hurts performance.

Interviewer: "Tell me your common SQL optimization methods." Candidate: "Don't use SELECT *, it's inefficient..."

Below we dive into the reasons SELECT * is slower and the related index concepts.

1. Reasons for Low Efficiency

Unnecessary columns increase data transmission time and network overhead.

Large unused fields (VARCHAR, BLOB, TEXT) cause extra I/O operations.

Using SELECT * prevents MySQL optimizer from applying the covering index strategy.

Unnecessary Columns Increase Transfer Cost

The database must parse more objects, fields, permissions, and attributes, adding parsing overhead.

Extra columns (e.g., logs, large text fields) increase network traffic, especially when client and server are on different machines.

Even on the same machine, TCP communication adds latency.

Large Unused Fields Increase I/O

When a column exceeds 728 bytes, MySQL InnoDB stores overflow data separately, causing an additional I/O read for each row.

Loss of Covering Index Optimization

SELECT * disables the possibility of using a covering index, which can retrieve all needed columns directly from the index without accessing the table data.

For example, with a table t(a,b,c,d,e,f) where a is the primary key and b has an index, a query that only needs a and b can be satisfied by the secondary index alone. Using SELECT * forces MySQL to read the full row from the clustered index after filtering, adding an extra B+‑tree lookup.

Since secondary index data is much smaller than the clustered index, many queries can be satisfied from memory without disk I/O, while accessing the clustered index may require disk reads, leading to orders‑of‑magnitude speed differences.

2. Extended Index Knowledge

Besides single‑column indexes, MySQL supports composite (union) indexes such as (a,b,c), which internally create indexes for (a), (a,b), and (a,b,c).

Composite Index (a,b,c)

Think of a composite index like a book's hierarchy: a is the first level, b the second, c the third. To use a lower‑level column, the higher‑level columns must be used first.

1) Reduce Overhead

Creating a composite index (a,b,c) actually creates three indexes, each adding write and storage overhead. However, for large tables, a well‑designed composite index can greatly reduce query cost.

2) Covering Index

SELECT a,b,c FROM table WHERE a='xx' AND b='xx';

MySQL can satisfy this query using only the index, avoiding a table lookup and reducing random I/O.

3) Higher Efficiency

With 10 million rows, a single‑column index may filter 10 % (1 million rows), requiring a table lookup. A composite index on (col1,col2,col3) can filter down to 1 % of that (10 000 rows), dramatically improving performance.

Is More Index Always Better?

No. Over‑indexing adds write overhead, consumes storage, and may not improve query speed for small tables or rarely used columns.

Small tables don’t need indexes; they add unnecessary overhead.

Columns rarely queried should not be indexed.

Frequently updated columns should avoid indexes due to write penalty.

Low‑cardinality columns (e.g., gender) provide little benefit.

More indexes increase maintenance cost and storage space.

3. Personal Takeaways

If you found this deep dive useful, feel free to give a like. In practice, many projects are small and don’t hit performance bottlenecks, so developers sometimes still use SELECT * out of habit.