A database query can feel fast when a table has only 100 records. Pages load quickly, API responses look fine, and database performance does not seem like something you need to worry about.
As the application grows, the situation can change. The table reaches 100,000 records and later 1 million. Pages that used to load quickly start taking longer, searches become slower, and API requests take more time.
The database has not suddenly become bad. It simply has more data to process, which means some queries may now require much more work than they did before.
This is where database indexing becomes important.
WHAT DATABASE INDEXING ACTUALLY MEANS
Developers often test queries with small datasets and assume they are already optimized. With only 100 rows, even an inefficient query can finish quickly because the database has very little data to examine.
For example, scanning 100 rows may not create any noticeable performance problem. Scanning a much larger table is a different situation because the amount of work can increase significantly.
An index gives the database another way to find the rows it needs instead of checking every row in the table.
For example:
CREATE INDEX IX_Products_CategoryId]
ON Products(CategoryId);
Now, when your application runs:
SELECT *
FROM Products
WHERE CategoryId = 5;
The database has an index it may be able to use to find the matching records more efficiently.
That does not mean you should create an index on every column. The better question is whether a particular index actually helps the queries your application runs.
A QUERY THAT IS FAST WITH 100 ROWS CAN BE SLOW WITH 100,000
Imagine an application with a products table containing 100 records.
You run:
SELECT *
FROM Products
WHERE CategoryId = 5;
With such a small table, the result may come back almost immediately.
Now imagine the same table contains 1 million products. If the database has to examine a large portion of those rows to find the matching products, the amount of work becomes much more noticeable.
This is why testing only with development data can give you a false sense of security. An application may work perfectly with a few hundred records and still develop database performance problems after production data grows.
When testing database performance, use data that is reasonably close to the workload you expect in production. A query working correctly is only part of the picture; you also need to understand how much work the database performs to return the result.
NOT EVERY INDEX MAKES A QUERY FASTER
An index can improve read performance, but it also comes with a cost. Indexes use storage and need to be maintained when data changes, so creating too many indexes can increase the cost of INSERT, UPDATE, and DELETE operations. SQL Server documentation also recommends avoiding speculative over-indexing because unnecessary indexes can slow data modifications.
For example, you might have a table with:
Id
CustomerId
Status
Email
CreatedAt
UpdatedAt
CategoryId
Country
You could create an index on every column, but that does not automatically make the application faster. Some indexes may never be used, while others may provide very little benefit for the actual query workload.
Good database optimization starts with understanding how the application accesses the data. Before adding an index, look at which query is slow, which columns are used for filtering or joins, whether sorting is involved, how many rows are being examined, and how many rows are actually returned.
The goal is to create indexes that support the queries that matter instead of adding indexes simply because a column looks important.
UNDERSTANDING THE TYPES OF INDEXES IN SQL
There are different types of indexes in SQL, and the right choice depends on how your application accesses the data.
A simple single-column index might look like this:
CREATE INDEX IX_Users_Email
ON Users(Email);
This can make sense when your application frequently searches users by email:
SELECT *
FROM Users
WHERE Email = 'user@example.com';
You can also create a composite index when queries commonly filter on more than one column:
CREATE INDEX IX_Orders_Customer_Status
ON Orders(CustomerId, Status);
For example:
SELECT *
FROM Orders
WHERE CustomerId = 1001
AND Status = 'Pending';
The order of columns in a composite index matters. The index should be designed around the way the application actually queries the data rather than around which columns simply appear important.
THE SQL QUERY OPTIMIZER DECIDES HOW TO RUN THE QUERY
Modern database systems have a SQL query optimizer that evaluates possible ways to execute a query and selects a plan based on factors such as available indexes, statistics, filters, joins, sorting, and estimated cost. In SQL Server, the Query Optimizer is cost-based and selects plans based on estimated processing cost.
For example:
SELECT *
FROM Orders
WHERE CustomerId = 1001
AND Status = 'Pending';
The optimizer may decide to use an existing index if it expects that to be the most efficient option. However, having an index available does not mean the optimizer will always use it, and using an index does not automatically mean the query will be fast.
A query can still be slow because of:
- Missing or unsuitable indexes
- Poor filtering
- Expensive joins
- Large result sets
- Inefficient sorting
- Functions applied to columns
- Incorrect assumptions about the data
This is where an execution plan becomes useful. It shows how the database chose to retrieve the data and can help you see whether the query is scanning a table, using an index, performing an expensive sort, or doing more work than expected.
SQL CODE OPTIMIZER TOOLS ARE NOT MAGIC
There are tools that can help analyze SQL queries and suggest possible improvements. They can be useful, especially when a query is complicated, but you should not change SQL blindly just because a tool recommends a different version.
For example:
SELECT *
FROM Orders
WHERE YEAR(OrderDate) = 2026;
Depending on the database engine and available indexes, applying a function to a column in a filter can make efficient index usage more difficult.
A range-based query may be more suitable:
SELECT *
FROM Orders
WHERE OrderDate >= '2026-01-01'
AND OrderDate < '2027-01-01';
The exact result depends on the database engine, indexes, statistics, and execution plan. The better approach is to compare the actual behavior of the queries rather than assuming that one version must always be faster.
In other words, do not optimize SQL based only on how the query looks. Check what the database is actually doing and use measurements to guide the change.
LARGE DATASETS CHANGE HOW YOUR APPLICATION SHOULD WORK
Indexes are only one part of handling large datasets. The way your application retrieves and sends data also becomes important as the number of records grows.
Imagine an admin dashboard containing 1 million orders. You probably do not want to run:
SELECT *
FROM Orders;
and send all 1 million rows to the API and browser.
The database has to process the request, the API has to handle the result, the server has to transfer the data, and the browser has to process it. Most importantly, the user probably only needs to see 25 or 50 records at a time.
This is where pagination becomes useful.
PAGINATION KEEPS LARGE RESULTS UNDER CONTROL
Instead of returning every record, request a smaller set:
SELECT *
FROM Orders
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY;
The next request can retrieve another page instead of loading the entire result set again.
Pagination helps keep the amount of data being processed and transferred under control. It is commonly useful for:
- Admin dashboards
- Product catalogs
- Order histories
- Customer lists
- Search results
- Reporting screens
For very large tables, traditional offset pagination can eventually become expensive, especially when requesting pages far into the result set. In those situations, keyset or cursor-based pagination may be a better fit, depending on the data and how users move through the results.
DON’T WAIT UNTIL THE DATABASE REACHES 100,000
A common mistake is to wait until performance becomes a problem before thinking about how the application will behave as its data grows.
During development, a table may contain only 100 records. In production, that same table can grow to hundreds of thousands or millions of rows, and queries that seemed harmless with a small dataset may become expensive.
You do not need to optimize everything from the first day. It is more useful to identify the queries that are likely to become expensive as the data grows and test them with realistic datasets.
Check the execution plans, monitor response times, and make changes based on actual measurements. This gives you a much better idea of where optimization is needed instead of adding indexes or changing queries without knowing whether they solve the real problem.
OUR TAKE
At Qodors, we see database performance as something that should be considered as an application grows. A query that works well with 100 rows may behave differently when the same table reaches 100,000 records. Testing only with small datasets can hide problems that may appear later in production.
Database indexing can help queries find data more efficiently, but adding indexes to every column is not the solution. Check which queries are slow, review execution plans, and choose indexes based on how the application actually uses the data.
For large datasets, the way data is retrieved also matters. Use pagination when users only need a small part of a large result set, and measure query performance before making changes. The goal is simple: use the right indexes and optimize the queries that actually need attention.
QUICK REFERENCE
- Check query performance as data grows.
- Use indexes where they actually help.
- Avoid unnecessary indexes.
- Use pagination for large datasets.
- Test and optimize based on real performance.
Don’t wait for performance problems. Find slow queries, check execution plans, and optimize where needed.
Database #SQL #DatabaseIndexing #SQLPerformance #QueryOptimization #SQLQueryOptimizer #LargeDatasets #Pagination #QodorsEdge
Written by the team at Qodors — we build and improve full-stack products for a living. →https://www.qodors.com/?utm_source=devto&utm_medium=post&utm_campaign=medium_database_indexing
Top comments (0)