Why Your SQL Queries Are Slow (And How to Fix Them)

It's a classic story: you write a query, it runs instantly on your test database with 50 rows, and you deploy it. Two months later, your app is crashing because that same query is now trying to sift through 5 million records.

SQL performance can feel like a dark art, but 90% of performance issues come down to a few common mistakes. In this post, we'll explore why queries get slow and how you can use SQL Boy's Visual Tools to spot the problems.

1. The SELECT * Trap

We've all done it. It's easy to write:

SELECT * FROM users WHERE id = 123;

But SELECT * is a performance killer for two reasons:

1. Network Overhead: You're fetching data you might not need. If your users table has a massive bio text field, you're dragging that across the network every time.
2. Index Inefficiency: Databases can often retrieve data directly from an index (Covering Index) if you only ask for specific columns. SELECT * forces the database to look up the full row on disk.

The Fix: Be specific.

SELECT id, username, email FROM users WHERE id = 123;

2. The Missing Index (The Library Analogy)

Imagine walking into a massive library to find a book titled "The Art of SQL", but there's no catalog system. You'd have to walk every aisle, look at every shelf, and check every single book. That's a Full Table Scan.

Without an index, your database does exactly this.

SELECT * FROM orders WHERE customer_id = 450;

If customer_id isn't indexed, the database reads every single order to find the ones for customer 450.

The Fix: Add an index on columns you frequently filter by.

CREATE INDEX idx_orders_customer ON orders(customer_id);

That said, "add an index" is not a universal answer. Indexes help reads, but they also add write overhead and storage cost. If a table receives heavy inserts or updates, every extra index makes those writes more expensive. Good performance work is about choosing the right indexes for the queries that matter most, not indexing every column in sight.

3. Non-SARGable Predicates

Another common slowdown is writing filters that prevent the database from using an index efficiently.

This version is often slow:

SELECT *
FROM users
WHERE LOWER(email) = '[email protected]';

If there is an index on email, wrapping the column in LOWER() can make that index much less useful.

A better strategy is usually one of these:

• normalize the data before storing it
• store a derived searchable version
• use a function-based index if your database supports it

The same warning applies to expressions like:

• WHERE YEAR(order_date) = 2025
• WHERE CAST(created_at AS DATE) = '2026-04-01'
• WHERE salary + bonus > 100000

Whenever you transform the indexed column in the predicate, ask whether the database can still seek efficiently or whether you just forced a scan.

4. Joins, Sorts, and Big Intermediate Results

Queries are not only slow because of missing indexes. They also get slow when the query shape produces far more data than the final answer needs.

Examples:

• joining two large tables before filtering
• grouping after an accidental many-to-many join
• sorting a huge dataset when only the top 20 rows are needed
• selecting wide text columns during an investigative query

This is why query tuning should start with row counts and query grain. If your query creates 50 million intermediate rows and only returns 20 final rows, the problem is often the shape of the query, not the database engine.

5. How to "See" the Problem: The EXPLAIN Command

You don't have to guess why a query is slow. SQL databases have a built-in feature called EXPLAIN (or EXPLAIN QUERY PLAN) that tells you exactly what they are doing.

On SQL Boy, we make this visual. When you run a query, you can switch to the "Explain" tab to see the execution plan.

Look for these keywords:

• SCAN TABLE: Bad. This usually means a full table scan.
• SEARCH TABLE: Good. This means it's using an index (Binary Search).

Try it yourself!

Copy this query into the SQL Boy Playground and check the Explain tab:

-- This will likely trigger a SCAN if 'salary' isn't indexed
SELECT * FROM employees WHERE salary > 100000;

When reading a plan, focus on a few questions:

1. Is the database scanning or seeking?
2. Which table is read first?
3. Is it sorting or hashing a large intermediate result?
4. Are joins happening on indexed columns?
5. Is the estimated row count wildly different from what you expect?

Those five checks catch a large share of real-world performance issues.

A Practical Tuning Workflow

If a query is slow in production, use a repeatable process:

1. capture the exact SQL and parameters
2. run EXPLAIN or EXPLAIN ANALYZE
3. identify the most expensive scan, join, or sort
4. reduce rows earlier with better filters or join order
5. add or refine indexes only after you understand the query shape
6. rerun the plan and compare

This order matters. Many teams jump straight to adding indexes and end up with index sprawl because they never fixed the underlying query design.

Summary

1. Never use SELECT * in production.
2. Index columns used in WHERE, JOIN, and ORDER BY.
3. Use EXPLAIN to verify your query is actually using those indexes.

Mastering these basics puts you ahead of many developers. Happy optimizing!

Related Articles

• Understanding Database Indexes for a deeper explanation of when indexes help and when they do not.
• Reading SQL Execution Plans for the next step once you are ready to inspect actual plan operators.
• SQL Optimization Techniques for a broader set of rewrites beyond the first three issues covered here.