Why Using SELECT * Is Inefficient in MySQL and How Indexes Improve Performance

1. Reasons for Low Efficiency

According to the latest Alibaba Java Development Manual, using SELECT * in table queries is prohibited because it forces the parser to handle all columns, adds parsing cost, may cause mismatches with result mappings, and transmits unnecessary data, especially large text fields.

Increases query analysis cost.

May lead to resultMap inconsistencies.

Unneeded fields increase network traffic.

Further analysis shows three main drawbacks:

1. Unnecessary columns increase data transfer and network overhead

The parser must resolve more objects, fields, permissions, and attributes, burdening the database.

Large unused columns (e.g., logs, icons) inflate the transmitted size, especially over TCP.

Even on the same machine, TCP communication adds latency.

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

When a row exceeds 728 bytes, MySQL InnoDB stores overflow data separately, requiring an additional I/O operation to read the row.

3. Loss of "covering index" optimization

SELECT *

prevents the optimizer from using covering indexes, a highly recommended strategy that can retrieve data directly from the index without accessing the clustered index.

Example: a table t(a,b,c,d,e,f) with primary key a and index on b. If only a and b are needed, the secondary index can satisfy the query without touching the clustered index. Using SELECT * forces an extra B+‑tree lookup, slowing the query.

2. Index Knowledge Extension

MySQL supports single‑column and composite (multi‑column) indexes. A composite index (a,b,c) implicitly creates indexes on (a), (a,b), and (a,b,c).

Composite Index Advantages

1) Reduced overhead

Each additional index adds write and storage cost, but a well‑designed composite index can replace several single‑column indexes, lowering overall overhead.

2) Covering index

For the query SELECT a,b,c FROM table WHERE a='xx' AND b='xx';, MySQL can satisfy the request entirely from the composite index, avoiding a table lookup and reducing random I/O.

3) Higher efficiency

Consider a table with 10 million rows and a composite index on (col1,col2,col3). If each condition filters 10 % of rows, the composite index reduces the candidate set to 1 % (≈100 000 rows) versus 10 % (≈1 000 000 rows) with only a single‑column index, dramatically improving performance.

Is more indexing always better?

The answer is no. Over‑indexing adds write overhead, consumes storage, and may not benefit low‑cardinality columns (e.g., gender). Indexes should be created on frequently queried, selective, and relatively static columns.

3. Personal Insights

The author, a seasoned MySQL developer, wrote this article to consolidate scattered knowledge about SELECT * pitfalls and index strategies, hoping readers can use the information in interviews and real‑world projects.

For further reading, see the recommended articles on high cohesion, Elasticsearch use cases, message queue selection, and permission design in front‑back separation architectures.