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

During many interviews candidates are asked about SQL optimization and quickly answer that SELECT * should be avoided, but few understand the deeper reasons. This article dives into why SELECT * hurts performance and how indexes can mitigate the issue.

Reasons SELECT * Is Slow

1. Retrieving unnecessary columns increases data transmission size and network overhead, especially when large text or BLOB fields are included.

2. The MySQL parser must process more objects, fields, permissions, and attributes, raising parsing cost.

3. Selecting all columns prevents the optimizer from using a covering index, forcing a second lookup on the clustered index.

4. Large unused fields (e.g., varchar , blob , text ) trigger extra I/O because MySQL stores overflow data separately once it exceeds 728 bytes.

Index Knowledge Extension

Composite indexes such as (a,b,c) actually create three index entries: (a) , (a,b) , and (a,b,c) . They allow queries that filter on the leading columns to use the index efficiently.

Advantages of Composite Indexes

Reduced Write Overhead: Fewer indexes mean less write cost and storage consumption.

Covering Index: When a query can be satisfied entirely from the index, the engine avoids a table (back) lookup, saving random I/O.

Higher Efficiency: More selective leading columns shrink the result set early, reducing the amount of data the engine must process.

However, more indexes are not always better. Over‑indexing adds maintenance cost, consumes disk space, and can degrade write performance.

Practical 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 , the optimizer can use the secondary index directly (covering index) without touching the clustered index.

If the query uses SELECT * , MySQL must first use the secondary index to locate rows, then read the full row from the clustered index, resulting in an extra B+‑tree lookup and slower performance.

SELECT a,b FROM t WHERE b = 'value';  -- can use covering index
SELECT * FROM t WHERE b = 'value';  -- forces extra lookup

When More Indexes Are Not Beneficial

Indexes should be created judiciously: avoid them on small tables, rarely queried columns, frequently updated columns, low‑cardinality fields (e.g., gender), and columns that would add significant storage or maintenance overhead.

Takeaways

Understanding the cost of SELECT * and leveraging composite/covering indexes can dramatically improve MySQL query performance. Choose columns explicitly, design indexes based on query patterns, and remember that more indexes do not automatically mean faster queries.