Why Using SELECT * Is Inefficient in MySQL and How to Optimize Queries

1. Reasons for Low Efficiency

The official Alibaba Java Development Manual explicitly mandates never using * in SELECT statements because it adds parsing cost, can cause mismatches with resultMap configurations, and transfers unnecessary columns, especially large text fields, over the network.

Unneeded columns increase data transmission time and network overhead, force additional I/O for large fields (VARCHAR, BLOB, TEXT) that exceed 728 bytes, and prevent the MySQL optimizer from applying the highly efficient covering index strategy.

1) Unnecessary Columns Increase Network Overhead

Using SELECT * forces the database to parse more objects, fields, permissions, and attributes, which burdens the parser and increases network traffic, especially when large text columns are included.

2) Large Unused Fields Cause Extra I/O

When a row contains fields larger than 728 bytes, MySQL stores the overflow elsewhere, requiring an additional I/O operation to read the data.

3) Loss of Covering Index Optimization

Covering indexes allow MySQL to satisfy a query using only the index without accessing the table rows. SELECT * forces a full table lookup, adding an extra B+‑tree traversal and significantly slowing the query.

Example: For a table t(a,b,c,d,e,f) with a as the primary key and an index on b , a query that only needs a and b can be satisfied by the secondary index alone. Using SELECT * forces a second lookup on the clustered index, doubling the tree traversals.

2. Extended Index Knowledge

Composite Index (a,b,c)

A composite index on (a,b,c) implicitly creates three indexes: (a), (a,b), and (a,b,c). It works like a hierarchical directory where you must use the higher‑level keys before accessing lower‑level ones.

1) Reduce Overhead

Each additional index adds write‑time and storage overhead, but a well‑designed composite index can dramatically reduce the cost of filtering large tables.

2) Covering Index

With a query such as SELECT a,b,c FROM table WHERE a='xx' AND b='xx'; , MySQL can retrieve the result directly from the composite index without a table lookup, eliminating random I/O.

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

3) Higher Efficiency

For a table with 10 million rows, a single‑column index might filter 10% (1 M rows) and then require a table lookup. A three‑column composite index can filter down to 1% of that (10 K rows), greatly improving performance.

SELECT col1,col2,col3 FROM table WHERE col1=1 AND col2=2 AND col3=3;

Is More Index Always Better?

Not necessarily. Small tables may not benefit from indexes, and indexes on rarely used, frequently updated, or low‑cardinality columns (e.g., gender) add maintenance cost and storage without performance gains.

3. Personal Reflections

The author emphasizes that while many developers still use SELECT * in small projects without performance bottlenecks, understanding the underlying costs is crucial for interviews and larger‑scale systems.

For readers who find this information useful, the author encourages sharing and acknowledges the source of the content.