Why SELECT * Slows Down MySQL and How to Write Faster Queries

1. Increased Disk I/O Overhead

When a query uses SELECT *, MySQL reads every column of the matched rows, including large TEXT, LONGTEXT, or BLOB fields. Because MySQL stores data on disk, reading more columns means more disk reads, which directly raises I/O cost, especially for tables that have grown from a few columns to dozens.

2. Increased Network Latency

Returning extra columns enlarges the result set, which can cause the TCP packet size to exceed the max_allowed_packet limit (default 64 MB) and require fragmentation. This adds extra transmission rounds and cumulative round‑trip time, even on a single machine.

More transmission rounds: Large fields may need to be split into multiple packets, increasing the number of RTTs.

Latency accumulation: Each packet still undergoes TCP handshakes and acknowledgments, adding microsecond‑level delays.

3. Inability to Use Covering Indexes

Consider a table user_info (InnoDB) with columns id, age, name, address. A composite index idx_age_name covers age and name: SELECT * FROM user_info WHERE age = 18; Because the query selects all columns, MySQL cannot satisfy it using the covering index alone; it must perform a back‑table lookup to fetch address. The execution plan shows the index is used, but a “Using index” flag is missing, indicating a table row fetch.

If only age and name are needed, the query should be written as SELECT age, name FROM user_info WHERE age = 18;. With the covering index, MySQL can retrieve the data directly from the index without accessing the table rows.

"Using index" indicates that the query is covered by the index, eliminating the need for a back‑table lookup.

Note that a covering index is not a separate index type; it is an optimization technique where all columns referenced in the SELECT list are present in the index, allowing MySQL to fetch results solely from the index structure.