When you use SELECT *, MySQL retrieves every column from a table — even if your application only needs a few of them. While this might seem convenient during development, it can seriously affect performance in production environments.
Why it’s a problem:
-
Increased I/O and memory usage: Retrieving unnecessary columns increases the size of the result set, consuming more memory on both the database server and client side.
-
Slower network transfer: The more data sent over the network, the longer it takes for queries to complete, especially when working with large tables or remote servers.
-
Reduced query optimization: MySQL’s query optimizer performs better when it knows exactly which columns are needed. With
SELECT *, the optimizer has to work harder, sometimes generating less efficient execution plans. -
Fragile code maintenance: If new columns are added to the table later, queries using
SELECT *will automatically return them — which might break application logic or display unwanted data.
Instead of:
SELECT * FROM customers;
( Use only required columns: )
SELECT name, email FROM customers;
This reduces the amount of data transferred and processed, especially in large tables.