56 SQL Queries That Will Destroy Your Database (Part 1 of 56)

This post is part of my learning journey based on a paid SQL performance resource, where I am documenting key insights in my own words for better understanding and revision.

Query #1: SELECT * on Large Tables

One of the first and most important lessons is about avoiding SELECT *, especially on large tables.

At first glance, SELECT * seems harmless and convenient because it retrieves all columns from a table. However, as data grows, this approach becomes inefficient and expensive. It forces the database to read and transfer more data than necessary, increasing memory usage, network load, and query execution time.

It can also prevent the use of optimized execution paths, like covering indexes, because the database must access the full table instead of only using the index.

The dangerous query:

SELECT *
FROM orders
WHERE customer_id = 1001;

SELECT *
FROM orders
WHERE customer_id = 1001;

SELECT *
FROM orders
WHERE customer_id = 1001;

In this case, even if the application only needs a few fields, the database still returns every column in the orders table. On large production systems, this unnecessary data transfer can significantly degrade performance.

Another important issue is maintainability. If new columns are added or existing ones change, SELECT * will automatically include those changes. This can unintentionally break application logic that depends on a fixed structure or specific fields.

The improved version:

SELECT order_id, customer_id, order_date, status, total_amount
FROM orders
WHERE customer_id = 1001;

SELECT order_id, customer_id, order_date, status, total_amount
FROM orders
WHERE customer_id = 1001;

SELECT order_id, customer_id, order_date, status, total_amount
FROM orders
WHERE customer_id = 1001;

This approach explicitly selects only the required columns. It reduces data transfer, improves performance, and makes the query more predictable and stable over time.

One-line rule

Always select only the columns you actually need — avoid SELECT * in production queries.