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

Efficiency Low Reasons

According to the latest Alibaba Java Development Manual (Taishan edition), using

*

in table queries is strictly prohibited; required fields must be explicitly listed.

Increases query parser cost.

May cause mismatches with resultMap configurations.

Useless fields increase network consumption, especially large text fields.

1. Unneeded columns increase data transfer and network overhead

Using

SELECT *

forces the database to parse more objects, fields, permissions, and attributes, heavily burdening the DB when the SQL is complex.

Network overhead grows because

*

may pull large text columns such as logs or IconMD5, which is especially noticeable when the DB and application are on different machines.

Even when the DB server and client run on the same machine, communication still uses TCP, adding extra latency.

2. Useless large fields (varchar, blob, text) increase I/O

When a field exceeds 728 bytes, MySQL InnoDB stores the overflow elsewhere, causing an additional I/O operation when the record is read.

3. Lose MySQL optimizer “covering index” opportunity

SELECT * eliminates the possibility of using a covering index, which is a highly recommended fast optimization strategy.

Consider a table

t(a,b,c,d,e,f)

where

a

is the primary key and

b

has an index. MySQL maintains two B+ trees: a clustered index storing

(a,b,c,d,e,f)

and a secondary index storing

(a,b)

. If a query only needs columns

a

and

b

, the secondary index alone can satisfy it.

When

SELECT *

is used, MySQL first uses the secondary index to filter rows, then accesses the clustered index to retrieve all columns, adding an extra B+ tree lookup and significantly slowing the query.

Because the secondary index contains far fewer columns, many cases allow a covering index to read data directly from memory, while the clustered index often requires disk I/O, leading to order‑of‑magnitude speed differences.

Index Knowledge Extension

The discussion above introduced secondary indexes; now we explore composite (joint) indexes.

Composite Index (a,b,c)

Creating a composite index

(a,b,c)

actually builds three indexes:

(a)

,

(a,b)

, and

(a,b,c)

.

Think of it like a book's hierarchy:

a

is the first‑level chapter,

b

the second‑level section, and

c

the third‑level subsection. To use a lower‑level index, the higher‑level one must be usable.

Advantages of Composite Index

1) Reduce overhead

Each additional index adds write‑operation cost and consumes disk space. For large tables, a well‑designed composite index can dramatically reduce overall overhead.

2) Covering index

For a query like

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

, MySQL can retrieve the needed rows directly from the index without touching the table, eliminating random I/O.

3) High efficiency

Assume a table with 10 million rows. With a single‑column index, each condition filters roughly 10 % (1 million rows), then a table lookup is required. With a composite index

(col1,col2,col3)

, the three conditions filter down to about 1 % (100 k rows), providing a huge performance boost.

Is more indexes always better?

The answer is no.

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

Columns that are rarely queried should not be indexed.

Frequently updated columns should avoid indexes because they degrade insert/update speed.

Low‑cardinality columns (e.g., gender) are ineffective for indexing.

More indexes increase maintenance cost.

Indexes consume additional storage space.

Takeaways

If you’re passionate about MySQL or just want to impress interviewers, understanding why SELECT * hurts performance and how proper index design can speed up queries is essential.