Which is faster/best? SELECT * or SELECT column1, colum2, column3, etc

sql

best-practices

performance

data-access

byNikita Barsukov·Oct 26, 2024

SELECT specific columns generally outperforms SELECT* because it minimizes data transfer which leads to faster queries. It also defends against unexpected issues arising from database schema changes.

/* Why select the whole supermarket when you only need apples? */ 
SELECT column1, column2 FROM your_table;

Choosing specific columns reduces server load and improves query efficiency. Mark it under SQL optimization best practices.

Speeding up data retrieval

By choosing specific columns, you leverage indexing, a crucial accelerator of data access. Browsing entire tables is like blindly searching a bookshelf, selecting columns is like grabbing a book you need.

Fortification against changes

SQL is not set in stone. Schemas evolve. Selecting specific columns makes your queries less susceptible to performance hits from future schema changes. Think of it as SQL's version of future-proofing.

Trimming the fat

SELECT * often transports data you don't need, akin to paying for shipping bricks just to get the gold coin hidden within them. Using specific columns reduces parse times and network data transport.

Safe and secure

By not accessing unnecessary data, you reduce exposure in case of a security breach. It's a reassuring thought - only taking what's needed!

Hidden hurdles of SELECT *

SELECT * can lead to data overutilization and index underutilization. It's like running a marathon with weights on; remove the excess and get going faster.

Don't pay the price of convenience

The cost of reading unnecessary data is often overlooked. But remember, every byte counts when you're dealing with large data sets - penny wise, pound foolish!