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

Interview snippet: The interviewer asks about common SQL optimization methods and why SELECT * should be avoided. The interviewee gives a superficial answer, prompting a deeper discussion.

1. Reasons for Low Efficiency

According to the latest Alibaba Java Development Manual (Taishan edition) MySQL section, using * in a SELECT statement is prohibited because:

It increases the cost of query parsing.

It may cause mismatches with resultMap configurations.

Useless columns increase network consumption, especially large TEXT fields.

1. Unnecessary columns increase data transfer time and network overhead

Using SELECT * forces the database to parse more objects, fields, permissions, and attributes, which adds load to the server, especially for complex queries.

Extra columns also increase network traffic; large text columns (e.g., logs, image hashes) can cause the transmitted data size to grow dramatically, which is noticeable when the DB and application are on different machines.

2. Large unused columns (VARCHAR, BLOB, TEXT) increase I/O operations

If a column exceeds 728 bytes, MySQL InnoDB stores the overflow off‑page, so reading such a row incurs an additional I/O operation.

3. Loss of MySQL optimizer’s “covering index” optimization

SELECT * prevents the use of covering indexes, which allow the optimizer to satisfy a query using only the index without touching the table data, resulting in much faster execution.

Example: a table t(a,b,c,d,e,f) with a as the primary key and an index on b . If the query only needs a and b , the optimizer can read the auxiliary index directly. Using SELECT * forces an extra lookup on the clustered index, adding another B‑tree traversal and slowing the query.

Because auxiliary indexes are usually much smaller than the clustered index, many queries can be satisfied from memory without disk I/O, leading to orders‑of‑magnitude performance differences.

2. Extended Index Knowledge

The article introduces composite (multi‑column) indexes, explaining that an index on (a,b,c) actually creates three indexes: (a) , (a,b) , and (a,b,c) .

Composite Index (a,b,c)

Think of the composite index as a hierarchy: a is the first level, b the second, and c the third. To use a lower‑level column, the higher‑level columns must be specified.

1) Reduce Overhead

Creating a composite index (a,b,c) actually creates three indexes, each adding write and storage overhead. For large tables, a well‑designed composite index can significantly reduce query cost.

2) Covering Index

For the composite index (a,b,c) , the following query can be satisfied entirely from the index:

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

This avoids a table lookup (back‑track), reducing random I/O and improving performance.

3) Higher Efficiency

With a table of 10 million rows, consider two queries:

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

If each condition filters out 90 % of rows, a single‑column index would still need to scan about 1 million rows, while a composite index (col1,col2,col3) would reduce the result set to roughly 10 000 rows, dramatically improving speed.

Is More Index Always Better?

No. Small tables may not need indexes; unnecessary indexes add write overhead, consume storage, and can degrade performance for frequently updated columns.

3. Personal Reflections

The author hopes readers find the detailed explanation useful and encourages sharing the article. He also admits that in his own small projects he sometimes still uses SELECT * because performance issues have not yet surfaced.

Source: https://urlify.cn/ZvM3qe