Database Indexing in Practice: Why Queries Get Slow and Which Index Fixes Them

Most slow applications we are asked to look at do not need a bigger server. They need the right index. A query that returns instantly with a thousand rows in development can take seconds in production once the table holds a few million, and the cause is almost always the same: the database has to read the whole table to find a handful of rows. To show what actually helps, we loaded two million orders into PostgreSQL 18 and measured typical queries before and after adding different indexes. Some results were what you would expect. A few were not.

What an Index Does

Without an index, the database answers a query by reading every row in the table and checking each one, which is called a sequential scan or full table scan. An index is a separate, sorted structure, usually a B-tree, that stores the values of one or more columns together with a pointer to the row. Because it is sorted, the database can jump straight to the matching values, the same way you find a name in a phone book without reading it from the first page.

That sorting is the key to everything that follows. An index helps when the query can use its order: an exact match, a range, or a sort in the same direction. When the query cannot use the order, the index sits there, takes up space, and slows down every insert and update without making any read faster.

The Test Setup

We used PostgreSQL 18.4 in Docker with an orders table of two million rows (256 MB) and a customers table of one hundred thousand rows. Orders are spread across two years and 100,000 customers. The status column is skewed the way it is in real shops: 90 percent completed, 6 percent cancelled, and only half a percent pending. Each query ran nine times with a warm cache, and the table shows the median execution time from EXPLAIN ANALYZE. On a busy server with a cold cache the gaps are usually larger, not smaller.

A difference of 40 ms against 0.2 ms looks small for a single request. Multiply it by every page view, every API call, and every background job, and it decides whether the database server sits at 10 percent CPU or at 100. The more interesting part is how we got to the right index, because the obvious first attempt often did not work.

Lesson 1: Index the Filter and the Sort Together

For the latest cancelled orders, the query filters on status and sorts by created_at. An index on status alone made no difference at all: 56.0 ms, against 55.6 ms without any index. Cancelled orders are 120,000 rows, so the database still had to fetch all of them and sort them to find the newest twenty. An index on status and created_at together returned the answer in 0.2 ms, because the rows are already stored in the right order inside the index and the database simply reads the first twenty.

For a customer's order history the effect was smaller but the same: an index on customer_id alone brought the query down to 0.24 ms, and adding created_at to it brought it to 0.19 ms and removed the sort step. With customers who have thousands of orders, that sort step becomes the expensive part.

Lesson 2: Column Order in a Composite Index Matters

A composite index is sorted by its first column, then by the second within each value of the first, like a phone book sorted by surname and then first name. The general rule is to put columns compared with equals first and columns used for ranges or sorting last. A query that only filters on the second column usually cannot use the index: with only an index on (customer_id, created_at) available, counting the orders of one day fell back to a full scan at 45.5 ms. A separate index on created_at answered it in 0.36 ms.

There is a nuance in newer versions. PostgreSQL 18 introduced skip scan, and MySQL has had a similar feature since 8.0.13. When the first column has only a few distinct values, the database can jump through each of them. In our test, an index on (status, created_at), with only five statuses, answered the one-day count in 0.49 ms even though the query never mentioned status. With 100,000 distinct customer IDs as the first column, that trick no longer pays off. Treat skip scan as a bonus, not as a design strategy.

Lesson 3: A Function on the Column Disables the Index

This was the most striking result. Counting the orders of one day with a range condition, created_at from midnight up to the next midnight, took 0.36 ms with an index on created_at. Writing the same condition as date(created_at) = '2026-03-15' took 175.9 ms with that very same index in place, nearly four times slower than a plain full scan without any index. The database cannot search a sorted list of timestamps for the result of a function, so it read the entire index and computed the date for every entry.

The same thing happened with email lookups. A query on lower(email) took 24.7 ms without an index and 24.8 ms with an ordinary index on email, which the database simply ignored. An expression index on lower(email) brought it to 0.07 ms. The same trap catches other common patterns:

• Date functions in filters, such as YEAR(created_at) = 2026 or DATE(created_at) = ?. Laravel's whereDate() and whereYear() produce exactly this kind of SQL. Use whereBetween() with a start and end time instead.
• Comparing a text column with a number, for example a phone or NIK column stored as VARCHAR compared without quotes. MySQL converts every row before comparing, and the index is skipped.
• Searches with a leading wildcard, such as LIKE '%budi%'. A B-tree can only search from the start of a value. For search inside text, use full-text search, or the pg_trgm extension in PostgreSQL.
• Case-insensitive lookups with lower() or upper(). Either store the value normalised, or create an expression index on the same expression the query uses. MySQL supports this through functional indexes since 8.0.13.

Lesson 4: Partial Indexes for the Rows You Actually Query

Operations dashboards rarely look at completed orders. They look at the small set still waiting to be processed. In PostgreSQL you can index only those rows with a condition, for example an index on created_at where status = 'pending'. In our test it was exactly as fast as the full index on (status, created_at), 0.36 ms against 0.37 ms, but it took 232 kB instead of 77 MB, because it only holds about ten thousand rows. It also stays small as the table grows, since orders leave the index once their status changes. MySQL does not support partial indexes, so there the full composite index is the option.

The Cost: Every Index Slows Down Writes

Indexes are not free. Each one must be updated on every insert, on every update that touches an indexed column, and on every delete. We inserted the same 500,000 orders into two identical tables, one with only the primary key and one with five additional indexes:

Writes became roughly eight times slower and storage more than doubled. For a table that is read a thousand times for every write, that trade is clearly worth it. For a log table, an import table, or anything that receives a constant stream of inserts, every extra index needs a reason. Unused indexes are pure cost, and they accumulate quietly in applications that have been running for years.

How to Find the Indexes You Need

1. 1Find the queries that cost the most in total, not just the slowest single query. In PostgreSQL, enable the pg_stat_statements extension and sort by total execution time. In MySQL, use the slow query log or the statement summaries in performance_schema.
2. 2Run EXPLAIN ANALYZE on each one, with realistic parameters, against a copy of production data. Look for Seq Scan on large tables, a Sort step on many rows, or a large gap between estimated and actual row counts. EXPLAIN ANALYZE really executes the query, so wrap UPDATE and DELETE statements in a transaction and roll it back.
3. 3Design the index around the whole query: columns compared with equals first, then the range or sort column. Before adding a new index, check whether an existing one can be extended instead.
4. 4Add it to production without locking the table. In PostgreSQL use CREATE INDEX CONCURRENTLY, which does not block writes but cannot run inside a transaction, so it needs a migration that runs outside one. In MySQL with InnoDB, ALTER TABLE ... ADD INDEX with ALGORITHM=INPLACE and LOCK=NONE runs online.
5. 5Measure again, then remove what is not used. In PostgreSQL, pg_stat_user_indexes shows indexes with idx_scan at zero; in MySQL, the sys.schema_unused_indexes view lists them. Check the statistics over a full business cycle, including month-end reports, before dropping anything.

Indexing is one of the cheapest performance improvements available: no new hardware, no rewrite, often a single line in a migration. It works best as a habit rather than a rescue: review the query plan when a feature adds a new query, and check the slowest queries every few months as the data grows. If your application has become slow and you are not sure where to start, our team can review the database and the queries behind it.