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

During a typical interview, candidates are often asked to list SQL optimization techniques, and one of the most common pieces of advice is to avoid using SELECT * . Although the recommendation is widely repeated, many developers only understand it at a superficial level without knowing the underlying reasons.

1. Reasons Why SELECT * Is Inefficient

Unnecessary columns increase data transfer time and network overhead. Retrieving all columns forces the database engine to parse more objects, fields, permissions, and attributes, which adds parsing cost, especially for complex queries.

Extra columns—especially large text fields such as VARCHAR , BLOB , or TEXT —increase the size of the result set, leading to higher network traffic and longer transmission time when the client and server are on different machines.

Even when the client and server reside on the same host, the MySQL protocol still uses TCP, so additional data incurs extra latency.

2. Large Unused Fields Cause Extra I/O

When a column exceeds 728 bytes, MySQL (InnoDB) stores the overflow data off‑page, meaning that reading a row with such a column triggers an extra I/O operation.

3. Loss of Covering‑Index Optimization

Using SELECT * prevents the optimizer from employing a covering index, a technique that allows the query to be satisfied entirely from the index without accessing the base table, resulting in significantly faster execution.

For example, consider a table t(a,b,c,d,e,f) where a is the primary key and b has an index. If a query only needs columns a and b , a covering index on (a,b) can return the result directly from the secondary index, avoiding a lookup in the clustered index.

If the query uses SELECT * , MySQL must first use the secondary index to filter rows and then read the full row from the clustered index, adding an extra B‑tree lookup and slowing the query.

2. Extension of Index Knowledge

Beyond the basic concept of a secondary index, MySQL supports composite (multi‑column) indexes, which are essentially a hierarchy of indexes: (a), (a,b), and (a,b,c) for a composite index (a,b,c) . To use a specific column in the composite index, all preceding columns must be referenced.

Advantages of Composite Indexes

1) Reduced Write Overhead

Each additional index adds write and storage overhead. A well‑designed composite index can replace multiple single‑column indexes, reducing the total number of indexes and therefore the write cost.

2) Covering Index

When a query selects only the columns that are part of a composite index, MySQL can satisfy the query directly from the index (covering index), eliminating the need for a table lookup and dramatically reducing random I/O.

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

This query can be answered entirely from the index (a,b,c) without accessing the base table.

3) Higher Selectivity

With a composite index, each additional column further narrows the result set. For a table with 10 million rows, a three‑column composite index that filters 10% per column reduces the candidate rows to 1% (≈100,000 rows), offering a substantial performance boost compared to using a single‑column index.

Are More Indexes Always Better?

The answer is no. Over‑indexing can increase storage consumption, slow down INSERT/UPDATE operations, and add maintenance overhead. Indexes should be created only on columns that are frequently queried, have high selectivity, and are not frequently updated.

Small tables often do not need indexes.

Columns rarely used in WHERE clauses should not be indexed.

Highly volatile columns (frequently updated) should avoid indexing.

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

Each additional index incurs extra storage and maintenance cost.

3. Personal Reflections

If you are reading this, you are either passionate about MySQL or enjoy fiddling with queries. I hope you gained useful insights; if so, feel free to give a like or share.

When asked if I ever use SELECT * in my own code, I admit I do—our projects are small, data volume is low, and we haven't hit performance bottlenecks yet, so we are a bit lax.

Now you can answer interviewers confidently without being caught off guard.