How to Fix a Slow SQL Query: A Practical Guide for Data Analysts

by

There is a specific kind of silence that happens when you run a query in front of someone. The spinner turns. You start talking to fill the gap. You mention that the table is quite large. Two minutes later you are apologising.

Most slow queries are not slow because the database is weak or because you have too much data. They are slow because of a handful of very common patterns, and once you learn to spot them you can usually cut the runtime dramatically without touching the server at all.

This guide walks through the seven causes I run into most often, with the fix for each one.

Start by finding out where the time actually goes

Guessing is the biggest time waster in query tuning. You change something, it feels faster, you move on, and the real problem is still sitting there.

Every database gives you a way to see its plan before it runs anything:

sql

EXPLAIN
SELECT p.patient_id, p.full_name, COUNT(v.visit_id) AS visit_count
FROM patients AS p
JOIN visits AS v ON v.patient_id = p.patient_id
WHERE v.visit_date >= CURRENT_DATE - INTERVAL 90 DAY
GROUP BY p.patient_id, p.full_name;

In PostgreSQL, use EXPLAIN ANALYZE to get real timings instead of estimates. In MySQL, EXPLAIN alone gives you the plan and newer versions support EXPLAIN ANALYZE too.

Reading a plan without a DBA degree

You do not need to understand every line. Look for three things:

  1. The word “scan” on a big table. A sequential scan or full table scan means the database read every single row. That is fine on a 500 row lookup table and painful on a 40 million row fact table.
  2. The row counts. Compare the estimated rows with the actual rows. If the database thought it would touch 200 rows and it touched 2 million, its statistics are stale and its whole plan is built on a bad guess.
  3. The widest step. In EXPLAIN ANALYZE, one step usually accounts for most of the time. Fix that one. The rest rarely matters.

Now the seven causes.

Cause 1: You are reading far more rows than you need

This is the most common one and the most boring. The query works, the logic is right, and it pulls three years of history to build a chart that shows the last month.

sql

-- Slow: reads everything, then throws most of it away
SELECT visit_date, COUNT(*) AS visits
FROM visits
GROUP BY visit_date
ORDER BY visit_date;

sql

-- Faster: filter first
SELECT visit_date, COUNT(*) AS visits
FROM visits
WHERE visit_date >= CURRENT_DATE - INTERVAL 30 DAY
GROUP BY visit_date
ORDER BY visit_date;

Ask yourself what the output is genuinely used for. A dashboard tile showing this month does not need 2019 data in the scan.

The same applies to columns. If you only need three fields, do not ask for forty.

Cause 2: Your filter column is wrapped in a function

This one is subtle and it destroys index usage. If you put a column inside a function in the WHERE clause, the database can no longer use an index on that column, because it would have to compute the function for every row first to know what matches.

sql

-- Slow: index on visit_date cannot be used
SELECT COUNT(*)
FROM visits
WHERE YEAR(visit_date) = YEAR(CURRENT_DATE);

sql

-- Fast: the column stays bare, so the index works
SELECT COUNT(*)
FROM visits
WHERE visit_date >= MAKEDATE(YEAR(CURRENT_DATE), 1)
  AND visit_date <  MAKEDATE(YEAR(CURRENT_DATE) + 1, 1);

The technical word for a query the index can use is “sargable”, which is short for “search argument able”. You do not need the word. You need the habit: keep the column on its own side of the comparison, and do the work on the other side.

Same problem, different disguise:

sql

-- Slow
WHERE UPPER(district_name) = 'PUNE'

-- Better: store the data consistently, or use a case insensitive collation
WHERE district_name = 'Pune'

sql

-- Slow: casting a date column to text to match a pattern
WHERE CAST(visit_date AS CHAR) LIKE '2026-03%'

-- Better
WHERE visit_date >= '2026-03-01' AND visit_date < '2026-04-01'

Note that even here, you can avoid hardcoded values by deriving the month boundaries from CURRENT_DATE if the report is meant to roll forward.

Cause 3: SELECT * on a wide table

SELECT * feels harmless in exploration and expensive in production. Three things happen when you use it on a table with fifty columns:

  • The database reads and returns data you will never look at, including large text and blob fields.
  • It rules out a covering index, which is an index that could have answered the query entirely from the index itself without visiting the table.
  • Anything downstream, like a dashboard tool or a pandas script, now has to move all of that over the network.

Naming your columns takes twenty extra seconds and often halves the runtime on wide tables.

Cause 4: Missing indexes, or the wrong ones

An index is a sorted lookup structure. Without one, finding rows that match a condition means checking every row.

As a starting rule, you want indexes on:

  • Columns used in join conditions, especially foreign keys
  • Columns used frequently in WHERE filters
  • Columns used in ORDER BY on large result sets

sql

CREATE INDEX idx_visits_patient_date ON visits (patient_id, visit_date);

Column order in a composite index matters

A composite index on (patient_id, visit_date) works like a phone book sorted by surname then first name. You can look up everyone with a given surname quickly. You cannot use it to find everyone with a given first name.

So this index helps a query filtering on patient_id, or on patient_id and visit_date together. It does very little for a query filtering only on visit_date.

Put the column you filter on most often, and most selectively, first.

More indexes is not better

Every index has to be updated on every insert, update and delete. A table with twelve indexes writes slowly. If you are on a reporting replica this matters less, but on a transactional database, adding indexes casually is how you slow down the application that feeds you the data.

Before you add one, check what already exists. Many “missing index” problems turn out to be an existing index you were not using because of cause number two.

Cause 5: Filtering after the join instead of before

When you join two large tables and then filter, you can end up building a huge intermediate result only to discard most of it.

sql

-- Builds the full join, then filters
SELECT p.patient_id, COUNT(*) AS visit_count
FROM patients AS p
JOIN visits AS v ON v.patient_id = p.patient_id
WHERE v.visit_date >= CURRENT_DATE - INTERVAL 30 DAY
  AND p.status = 'active'
GROUP BY p.patient_id;

Modern query planners often push those filters down automatically, so this is not always a problem. But when the planner gets it wrong, or when the logic is complex, doing it yourself with a CTE makes the intent explicit and gives the planner much less room to guess badly:

sql

WITH recent_visits AS (
    SELECT patient_id, visit_id
    FROM visits
    WHERE visit_date >= CURRENT_DATE - INTERVAL 30 DAY
),
active_patients AS (
    SELECT patient_id
    FROM patients
    WHERE status = 'active'
)
SELECT a.patient_id, COUNT(r.visit_id) AS visit_count
FROM active_patients AS a
JOIN recent_visits AS r ON r.patient_id = a.patient_id
GROUP BY a.patient_id;

One caveat worth knowing: in some databases a CTE is an optimisation fence, meaning the planner treats it as a separate step and will not push filters through it. That can help you or hurt you. Test both versions rather than assuming.

Cause 6: DISTINCT is covering up a join problem

If you reached for SELECT DISTINCT because your row count looked wrong, stop. DISTINCT on a large result set is expensive, and duplicated rows are almost always a symptom of a join that fans out.

The usual culprit: you joined a one to many relationship and forgot that every parent row now repeats once per child row.

sql

-- Duplicates every patient once per visit, then dedupes expensively
SELECT DISTINCT p.patient_id, p.full_name
FROM patients AS p
JOIN visits AS v ON v.patient_id = p.patient_id
WHERE v.visit_date >= CURRENT_DATE - INTERVAL 30 DAY;

sql

-- Asks the real question: which patients have at least one recent visit
SELECT p.patient_id, p.full_name
FROM patients AS p
WHERE EXISTS (
    SELECT 1
    FROM visits AS v
    WHERE v.patient_id = p.patient_id
      AND v.visit_date >= CURRENT_DATE - INTERVAL 30 DAY
);

EXISTS can stop as soon as it finds one matching row. The join has to find all of them and then sort the whole thing to remove duplicates.

Cause 7: Leading wildcards and OR conditions

sql

-- Cannot use an index: the pattern has no fixed starting point
WHERE patient_name LIKE '%sharma%'

An index is sorted, so it can jump straight to entries starting with “sharma”. It cannot help you find “sharma” in the middle. If you genuinely need this, you want a full text index, not a LIKE.

OR across different columns has a similar effect, because the database often cannot use one index to satisfy both sides:

sql

-- Often slow
SELECT * FROM patients
WHERE phone_number = '9876543210' OR alternate_phone = '9876543210';

sql

-- Often faster: two indexed lookups combined
SELECT patient_id, full_name, phone_number FROM patients
WHERE phone_number = '9876543210'
UNION
SELECT patient_id, full_name, alternate_phone FROM patients
WHERE alternate_phone = '9876543210';

Test it. This one depends heavily on your database and your data distribution.

When the query is fine and the design is the problem

Sometimes you tune everything and the query is still slow, because you are asking a 50 million row table to recompute the same aggregation every time a dashboard loads.

At that point the answer is not a better query. It is a summary table:

sql

CREATE TABLE daily_visit_summary AS
SELECT
    visit_date,
    district_name,
    COUNT(*)                     AS total_visits,
    COUNT(DISTINCT patient_id)   AS unique_patients
FROM visits
GROUP BY visit_date, district_name;

Refresh it on a schedule, point the dashboard at it, and a four minute load becomes instant. You trade freshness for speed, which for most reporting is a trade worth making.

A checklist you can run through in five minutes

  1. Run EXPLAIN and find the step doing the most work.
  2. Is there a date or ID filter you could add without changing the answer?
  3. Are any filter columns wrapped in functions? Unwrap them.
  4. Replace SELECT * with the columns you need.
  5. Check whether join and filter columns are indexed.
  6. Is DISTINCT hiding a fan out? Try EXISTS.
  7. Is this the same aggregation being recalculated all day? Pre-aggregate it.

Measure, do not guess

The one habit that separates people who are good at this from people who are not: they time things.

Run the query, note the time. Make one change. Run it again, note the time. Keep a small text file with the numbers. It takes almost no effort and it stops you from carrying around superstitions about what is fast, which is otherwise exactly what happens.

Query tuning looks like deep expertise from the outside. Most of it is just this: look at the plan, reduce the rows touched, let the indexes do their job, and check whether the question you are asking needs to be asked from scratch every single time.

Leave a Comment