Why Using SELECT * Is Inefficient in MySQL and How Proper Indexing Improves Performance

During a technical interview, the candidate is asked about common SQL optimization techniques, specifically why using SELECT * is discouraged.

1. Reasons SELECT * Is Inefficient

According to the latest Alibaba Java Development Manual, using * in a query adds unnecessary parsing overhead, may cause mismatches with resultMap configurations, and transmits unused columns—especially large text fields—over the network.

Increases the cost for the query parser.

Leads to inconsistencies between selected fields and mapping configurations.

Unnecessary columns increase network consumption, particularly for TEXT or BLOB types.

1.1 Extra Data Transfer and Network Overhead

The database must parse more objects, fields, permissions, and attributes, which burdens the server when the SQL is complex.

Unneeded large columns (e.g., logs, image hashes) inflate the data size, causing significant network overhead when the client and server are on different machines.

Even on the same machine, MySQL uses TCP, so additional data still incurs transmission time.

1.2 Increased I/O for Large Unused Columns

When column values exceed 728 bytes, MySQL InnoDB stores overflow data separately, requiring an extra I/O operation to read the full record.

1.3 Loss of Covering Index Optimization

Using SELECT * prevents the optimizer from applying the "covering index" strategy, 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. Selecting * forces MySQL to read the primary (clustered) index after the secondary index, adding an extra B+‑tree lookup and slowing the query.

2. Extending Index Knowledge

Composite (multi‑column) indexes store multiple prefix indexes: an index on (a,b,c) implicitly creates indexes on (a) , (a,b) , and (a,b,c) . This hierarchy works like a book’s table of contents, where you must navigate higher‑level entries before lower‑level ones.

2.1 Advantages of Composite Indexes

1) Reduced Overhead

Creating a composite index (a,b,c) is equivalent to creating three separate indexes, but it avoids the extra write and storage cost of maintaining three independent indexes.

2) Covering Index

When a query selects only the indexed columns, MySQL can satisfy the request directly from the index without a table lookup:

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

This eliminates random I/O and greatly speeds up the query.

3) Higher Efficiency

With a three‑column composite index, each additional condition dramatically reduces the result set. For a table of 10 million rows, filtering 10 % per condition reduces the candidate rows to 100 000 after the first column, then to 10 000 after the second, and finally to 1 000 after the third.

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

2.2 When Not to Over‑Index

Small tables rarely benefit from indexes; they add maintenance overhead.

Columns that are seldom queried should not be indexed.

Highly volatile columns (frequent updates) degrade write performance when indexed.

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

Each additional index increases storage consumption and maintenance cost.

3. Personal Takeaways

The author hopes readers who are passionate about MySQL or enjoy tinkering with databases find the detailed analysis useful and encourages sharing the knowledge rather than “free‑riding.”

Despite often using SELECT * in small projects without performance bottlenecks, the author now emphasizes disciplined column selection and proper indexing for scalable applications.