SELECT * Elimination

Before (Inefficient Query)

SELECT * 
FROM orders 
WHERE customer_id = 12345;

After (Optimized Query)

SELECT order_id, order_date, total, status 
FROM orders 
WHERE customer_id = 12345;

Why This Optimization Works

1. Data Transferi: SELECT * transfers all columns over the network. On a table with 50 columns including large TEXT/BLOB fields, this is wasteful when you only need 3-4 columns.

2. Index-Only Scansi: If selected columns are in an index, the database can satisfy the query entirely from the index without touching table data.

3. Memory Usagei: Less data means less memory usage for result sets, especially important for large result sets.

Note on Data Transfer: The time it takes to transfer data from the database to the application is directly proportional to the amount of data being sent over the network. Whether the database takes 10ms or 100ms to process the query, the actual bottleneck is often the network transfer of the result set. A query returning 100 rows with 50 columns will transfer significantly more data than a query returning the same 100 rows with only 5 columns—regardless of how fast the database processes the query internally.

Best Practices

-- Bad: SELECT *
SELECT * FROM orders WHERE status = 'shipped';

-- Good: Explicit columns
SELECT order_id, customer_id, order_date, total, status 
FROM orders WHERE status = 'shipped';

-- Best: If you need all columns later, fetch by primary key
-- First: Get IDs only
SELECT order_id FROM orders WHERE status = 'shipped';
-- Then: Fetch full records individually
SELECT * FROM orders WHERE order_id = ?;

When You Might Need SELECT *

• Database dumps/backups
• Generic reporting tools
• Dynamic column selection
• Debugging/diagnostics