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

This is Query #2 in the series, and it focuses on a very common performance issue: missing indexes on JOIN columns.

In SQL, JOIN operations are used to combine data from multiple tables. But behind the scenes, the database has to find matching rows between those tables. This matching process becomes slow if the join column is not indexed.

Query #3: Missing Index on a JOIN Column

The dangerous query:

SELECT o.order_id, o.order_date, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'pending';

SELECT o.order_id, o.order_date, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'pending';

SELECT o.order_id, o.order_date, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'pending';

Why is this dangerous

When the database executes a JOIN, it needs to compare rows between the two tables using the join condition:

o.customer_id = c.customer_id

o.customer_id = c.customer_id

o.customer_id = c.customer_id

If orders.customer_id is not indexed, and the database has no fast way to find matching rows. Instead, it may:

• Scan the entire orders table

• Compare each row manually

• Repeat this process for the matching logic

This is called a sequential scan, and it becomes very slow as the table grows.

In small development datasets, this may not be noticeable. But in production systems with millions of rows, it can cause serious performance degradation and slow query response times.

The fixed version

To solve this, we create an index on the foreign key column:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Then run the same query:

SELECT o.order_id, o.order_date, c.customer_name
FROM orders oJOIN customers c 
ON o.customer_id = c.customer_idWHERE o.status = 'pending';

SELECT o.order_id, o.order_date, c.customer_name
FROM orders oJOIN customers c 
ON o.customer_id = c.customer_idWHERE o.status = 'pending';

SELECT o.order_id, o.order_date, c.customer_name
FROM orders oJOIN customers c 
ON o.customer_id = c.customer_idWHERE o.status = 'pending';

One-line rule

Always index columns used in JOIN conditions, especially foreign keys.