Skip to main content

Why Your SQL Query Slows Down as the Table Grows

Long rows of warehouse shelving representing a large table the database has to search through
Try the Tool
SQL Query Builder & Explainer
Build SQL visually or paste queries for plain-English explanations

The query worked fine for months. It ran in a few milliseconds on your laptop, it ran fine in staging, and it shipped without a second thought. Then the orders table crossed a few million rows and the same query started taking eight seconds, the page that depended on it began timing out, and nobody had changed a line of code.

Nothing broke, exactly. The query was always doing too much work, and a small table hid it. This guide covers why that happens, the handful of query shapes that quietly bypass an index, and a repeatable way to check whether your own query is one of them.

Small Tables Forgive Everything

On a table with ten thousand rows, reading every row is cheap. The database can scan the whole thing in a blink, so a query that ignores your index and a query that uses it finish in roughly the same time. You get no signal that anything is wrong.

The cost of a full scan grows in direct proportion to the table. Ten times the rows means roughly ten times the work, while a lookup through an index grows far more slowly, because the index is organized to jump straight to the matching rows. The gap between the two is invisible at small sizes and enormous at large ones.

That is why performance bugs in SQL tend to show up late. The query was never fast, it was only small. By the time the data is big enough to expose the problem, the original author has moved on and the code looks perfectly reasonable.

What an Index Actually Does

An index is a separate, sorted structure that points back at the rows in the table. Think of the index at the back of a printed book: instead of reading every page to find a topic, you look it up alphabetically and jump to the right page. The general idea is described well in the Wikipedia article on database indexes, and most relational databases use some form of a balanced tree to do it.

The important detail is the word sorted. An index is useful because the values are stored in order, so the database can binary search for the one you want. Anything that stops the database from comparing your search value directly against the stored values stops it from using that ordering.

Almost every case of an ignored index comes down to that one idea. The next few sections are the most common ways a query breaks it without looking like it does.

Long rows of warehouse shelving with labeled bins in a distribution center Photo by panumas nikhomkhai on Pexels

A Function on the Column Hides It From the Index

This is the most common culprit. Suppose you have an index on the created date and you write a filter like this: the year of the created date equals 2026. It reads naturally, and it forces the database to compute the year for every single row before it can compare anything.

The index stores raw dates, not years. Because the filter wraps the column in a function, the database cannot use the sorted order and falls back to scanning the whole table. The same thing happens with lowercasing a name column to compare it, trimming spaces, or adding a number to a column inside the condition.

The fix is almost always to move the work to the other side of the comparison. Instead of asking for the year to equal 2026, ask for dates greater than or equal to the first of January and less than the first of January the next year. Now the column stands alone, the index applies, and the result is identical. Queries written so the index can be used are often called sargable, a clumsy word for a very practical habit.

Leading Wildcards Break Prefix Searches

A search for names that start with the letters "smi" can use an index, because all of those values sit next to each other in sorted order. A search for names that contain "smi" anywhere cannot, because matches are scattered all through the index.

The difference is a single percent sign. A pattern that begins with a wildcard forces a scan of every value, while a pattern that begins with literal characters lets the database jump to the right spot and read a short run of entries.

If you genuinely need to search inside text, a plain index is the wrong tool. Most engines offer full-text search for this, and the PostgreSQL documentation has a thorough section on it. For simple cases, ask whether the search can be turned into a prefix match, which is far cheaper.

Mismatched Types Cause Silent Conversions

A column stores phone numbers as text, and your query compares it to a number. A column stores IDs as integers, and your application sends them as strings. The query still returns the right rows, so nothing looks wrong.

Behind the scenes, the database converts one side so the types can be compared. If it has to convert the column rather than your value, it is effectively wrapping every stored row in a function, and we are back to the first problem. The index goes unused and the scan begins.

The cure is boring and effective: make sure the value you pass in has the same type as the column. Check what your database driver sends, since some libraries bind everything as text by default. This one is easy to miss because the code reads correctly and the results are correct.

Open notebook on a desk with handwritten diagrams and a pencil beside it Photo by Devansh Kumar on Pexels

OR Conditions and Low-Selectivity Columns

An OR across two different columns can prevent a single index from covering the filter, since the database may need to look in two places and combine the results. Some engines handle this well and others fall back to a scan. If a query with OR is slow, try splitting it into two queries combined with UNION and compare the timing.

There is also the question of selectivity. An index on a column with only a few distinct values, such as a status flag with three options, helps less than you would hope. If half the table has the same value, the database may decide that reading the whole table is cheaper than bouncing between the index and the rows, and it is often right.

Indexes are not magic and they are not free. Each one takes space and slows down writes, so the goal is not to index everything. It is to index the columns your slow queries actually filter and join on, and to write those queries so the index can be used.

SELECT Star and Sorting Without an Index

Asking for every column when you need three costs more than it looks. It moves more data across the wire, and it can prevent the database from answering the query from the index alone. When all the columns you need live inside the index, the database never has to touch the table at all, which can be dramatically faster.

Sorting is a related trap. An ORDER BY on a column with no helpful index forces the database to collect every matching row and sort them in memory, or spill to disk when there are too many. Pair that with a LIMIT and you can wait for a huge sort just to return ten rows.

When an index matches both the filter and the sort order, the database can read rows already in order and stop after the first ten. That is the difference between milliseconds and seconds on a large table. The long-form book Use The Index, Luke covers this combination in depth and is free to read online.

"Almost every slow query I get asked about was written by someone who only ever tested it on a hundred rows. The query was never wrong, it just had never been asked to scale." - Dennis Traina, founder of 137Foundry

Read the Plan Instead of Guessing

Every major database can tell you how it intends to run a query before it runs it. This is the execution plan, and it is the single most useful tool for this whole topic. You put the word EXPLAIN in front of the query and read what comes back.

The thing to look for is the access method. A line that says sequential scan or full table scan means the database is reading everything. A line that mentions an index scan or index seek means it is using one. Next to it you will usually find an estimate of how many rows it expects to touch, and a big number there is your warning sign.

The details differ by engine, so keep the reference for yours nearby, whether that is the SQLite documentation or the MySQL site. Run the plan against a table with realistic data, since the planner makes different choices on tiny tables, and a plan taken from a nearly empty development database can look perfectly healthy while production burns.

A Faster Way to Review a Query Before It Ships

Reading plans is a skill, but you can catch most of these patterns by reading the query itself with the right checklist in mind. Scan the WHERE clause for functions wrapped around columns, check every LIKE for a leading wildcard, and look at the types on both sides of each comparison.

A plain-English explanation of a query helps here, especially for one you inherited or generated. When each clause is described in words, a filter like "convert every created date to a year, then compare" stands out as the expensive step it is, in a way the original syntax does not.

The SQL Query Builder & Explainer, a free tool from EvvyTools, does exactly this. Paste a query and it returns a plain-English translation with performance warnings and optimization suggestions, and you can also build a query visually from table definitions, joins, filters, and sorting. It does not see your data or your real indexes, so treat its warnings as prompts to check the plan, not as a verdict.

A Short Checklist Before You Blame the Hardware

When a query that used to be fast gets slow, work through these in order. They solve most cases without adding a single server.

  • Run EXPLAIN on realistic data and look for a full scan on the large table.
  • Remove any function wrapped around an indexed column in the WHERE clause, and rewrite it as a range.
  • Replace leading wildcards with prefix matches, or move text search to full-text indexing.
  • Match the type of every passed value to the type of its column.
  • Select only the columns you need, and make sure sorted queries have an index that matches the sort.

If you want more practical walkthroughs on databases and development, the EvvyTools blog has plenty in the same spirit, and the tools directory lists every free utility on the site. The short version: small tables hide bad queries, so write the filter so the index can be used, check the plan on real data, and let the numbers tell you when the work is growing faster than the table.

Honey-Do Tracker — home maintenance for landlords and property managers
Share: X Facebook LinkedIn
137 Foundry — custom app building studio