Database indexes for search pages after real traffic

Why early indexes stop fitting real search use

Teams usually add indexes before launch based on mockups, test data, and a few expected search flows. That's a sensible start, but it's still a guess. Once real people use the page, their behavior often moves in a different direction.

Search pages change quickly after launch. New filters appear. Old filters stay visible, but almost nobody uses them. A sort option that seemed minor in planning can become the default choice for half your traffic. If your index was built for "category + newest" but users now search "price range + in stock" and sort by rating, the database does more work than you planned.

That's why indexes on search pages age faster than many teams expect. They're tied to behavior, and behavior shifts as soon as users bring real goals, messy data, and time pressure.

You can often spot the shift before anyone opens an explain plan. Some filter combinations feel slower than others. One sort option gets worse as the catalog grows. Support hears that search "sometimes hangs" even though the rest of the site feels fine. Product teams may also notice that users rely on narrow filters, repeated searches, or combinations nobody mentioned before launch.

Picture a marketplace that launches with a simple path: browse by category and sort by newest. Two months later, shoppers care more about availability, delivery date, and price bands. The old index still exists, but it no longer matches the path most people take. The database starts scanning, sorting, or hopping through extra rows just to answer routine searches.

That gap is the real issue. Early tuning reflects product assumptions. Post-launch tuning should reflect production filter patterns, actual sort usage, and where the database spends time now. Otherwise, teams keep polishing the wrong query path while the busiest searches stay slow.

Start with production data

Begin with what people actually do on the search page. Pre-launch index choices usually come from guesses. Real traffic changes the picture fast. Users may ignore half your filters, hammer one price range, and sort by "newest" far more than anyone expected.

Pull a few days or weeks of production queries and group them by shape. Look at which filters appear most often, which filter pairs show up together, and which sort orders users pick after filtering. A search for category + price + newest is a different workload from category + rating + lowest price, even if both hit the same table.

Slow queries matter, but volume matters just as much. A query that runs 40 ms slower and happens 50,000 times a day deserves more attention than a rare search that spikes once an hour. Separate common paths from edge cases before you touch any index.

A small working set is usually enough:

• top filter combinations by count
• top sort orders for each common combination
• slow query samples for each pattern
• median and p95 latency by pattern
• rows scanned versus rows returned

That last number saves a lot of wasted effort. If a search returns 20 rows but scans 200,000, the database is telling you the current index does not match the filter and sort pattern very well.

Use the production tools you already trust. Query logs help. Slow query logs help more. If you already track database latency in Grafana or trace requests in Sentry, pull those samples too and match them to real search actions. This is a practical place to start because tuning gets easier when the numbers come from live behavior instead of theory.

The goal of this step is simple: reduce a messy stream of requests into a short list of search patterns worth fixing. Without that list, teams spend days tuning searches nobody uses and miss the sort order that burns CPU all day.

Turn raw logs into clear search patterns

Raw query logs look messy, but most search traffic usually falls into a handful of repeated patterns. One user types an exact ID and wants one result fast. Another opens a wide list, adds two filters, and sorts by newest. Those are different jobs, and they usually need different indexes.

Start by grouping searches by the filters people actually combine. Ignore rare one-off combinations at first. If 38% of searches use category + price range, and 24% use status + date range, those are better candidates than a long tail of custom mixes that appear twice a week.

For each query, track four things:

• which filters appear together
• whether the search is broad browsing or exact lookup
• which sort order follows that filter set
• how often it runs and how long users wait

Broad browsing and exact lookup should not sit in the same bucket. A lookup by email, SKU, username, or order number often needs a direct path to one row or a tiny result set. Browsing queries usually touch a larger slice of data and then sort it. Teams often blur these together and build one compromise index that fits neither case well.

Sort choice changes the picture more than many people expect. A filter on status might look cheap by itself, but status + sort by created_at can behave very differently from status + sort by price. If users almost always pair one filter set with one sort, treat that as one search pattern.

Then rank the patterns. Traffic count alone is not enough. A query that runs 500 times a day and adds 2 seconds of wait time can hurt more than a query that runs 5,000 times and returns in 40 ms. Put frequency next to latency and the expensive patterns rise to the top quickly.

This is where search tuning starts to feel manageable. You stop tuning for guesses made before launch and start tuning for the searches users actually run.

Check how the database answers those queries now

A slow search page can look fine in development because small tables hide bad query plans. Production does not. Take the search requests that run most often, then run EXPLAIN or EXPLAIN ANALYZE on those exact queries.

Use real inputs from logs or query history. Placeholder tests miss the point. If users usually filter by one category, a narrow price range, and sort by newest, test that. A database can pick a very different plan for a common value than for a rare one.

For each busy query, write down a few facts:

• how many rows the database scans
• how many rows it returns
• whether it uses an index or reads the whole table
• whether it adds a separate sort step

The gap between rows scanned and rows returned tells you where the waste is. If a query scans 60,000 rows to send back 20, the index probably matches only part of the filter. Sometimes the index helps with filtering, but the database still reads a large set before it can apply LIMIT.

Sort work often causes the hidden cost. A search page may ask for 20 products, but the database may still sort thousands of matching rows first. If the plan shows an extra sort step or a temp sort area, the current index does not match the sort order well enough.

Take a simple case. Users search for in-stock items in one brand and sort by created_at DESC. If the index starts with brand_id but leaves out created_at, the database may filter well and still sort a large result set afterward. The page feels fine on a small table, then gets slower as the table grows.

Save one plan for each common filter and sort pattern. Those snapshots matter later when you test index changes and check whether scanned rows actually drop.

Change indexes one step at a time

Changing indexes all at once is how teams get lost. Pick one search pattern that brings the most traffic, tune for that, and measure the result before touching anything else. That keeps the work tied to user behavior instead of theory.

Start with the search people run all day, not the rare admin filter that looks dramatic in a query log. If most users filter by status and category and then sort by newest, start there. The fancy low-volume report can wait.

A simple order works well:

1. Find the busiest filter and sort pair in production.
2. Build the index with exact-match columns first.
3. Add sort columns only if people use that sort often enough to justify the extra index size.
4. Test again after the change.
5. Remove duplicate or near-duplicate indexes when the new one covers the same job.

Column order matters more than many teams expect. If a query filters on category = 'books' and status = 'active' and then asks for price > 20, the database can usually use an index better when exact filters come first and the range comes later. If you lead with the range column, the index often helps less.

Sort columns need the same discipline. If users sort by created_at on 80% of searches, adding that column may cut response time a lot. If only a few users sort by rating once a day, that extra index can cost more than it saves.

Suppose your search page uses (status, brand, created_at) today, but production shows users mostly search by (status, category) and sort by price. In that case, an index on (status, category, price) may do a much better job for the traffic that matters. After testing, the old index may turn into dead weight.

That cleanup step matters. Duplicate indexes waste disk, slow writes, and make future tuning harder. Keep the indexes that earn their place.

A simple post-launch example

A small catalog often launches with one simple path: browse by category. The team adds an index on category_id, and the page feels fast while traffic is light.

A month later, people search like real shoppers. They open a category, tick "in stock", set a price ceiling, and sort by newest. The page still works, but response time jumps because the database now does extra work after it finds the category rows.

With only an index on category_id, the engine may read thousands of products in that category, throw out out-of-stock items, check the price filter, and then sort what remains by created_at. On a big catalog, that means more reads and more memory spent on sorting.

You can usually see the shift in plain numbers. A query that felt quick at launch starts creeping up as the table grows. The SQL did not suddenly get worse. User behavior changed, and the index no longer matches it.

A better fix is often one compound index built for the path people use most, such as category_id, in_stock, created_at DESC. That lets the database jump into a smaller slice of products and read them in the order the page already needs. If many users also set a max price, the database checks price on that smaller set instead of the whole category.

Rare searches can stay slower for now. If only a small share of users sort by lowest price or stack five filters at once, more indexes may not pay off yet. Every index adds write cost, storage use, and more choices for the planner.

Once production filter patterns settle, this work gets easier. Keep the old browsing index if it still helps. Then add one index for the most common filtered sort, measure again, and leave the uncommon paths alone until the data says otherwise.

That's why search-page indexes usually need a second pass after launch, not just a good guess before it.

Mistakes that waste tuning time

One of the fastest ways to waste effort is to treat every filter in the UI as a reason for a new index. A search page might have ten checkboxes, three date ranges, and two sort options. That does not mean you need an index for all of them.

Real users usually stick to a small set of patterns. Maybe most people filter by status and date and then sort by newest. If you add indexes for every field anyway, you slow writes, use more disk, and give the planner more choices to sort through.

Staging data causes another common mistake. Test data often looks neat, small, and evenly distributed. Production data is messy. One filter value may match 40% of rows, another only 12 rows, and one sort order may dominate almost everything.

That difference matters. A query that looks fast on staging can turn slow the day real traffic hits broad filters, empty searches, and uneven data.

Teams also lose time when they trust a single query plan. They test one input, see an index scan, and call it done. Then a different input arrives, the database switches plans, and the same page gets much slower.

Check a few real cases instead:

• a narrow filter that returns very few rows
• a broad filter that returns thousands
• the most common sort order
• a search with no optional filters at all

Those cases often share the same SQL shape but behave very differently.

Too many indexes hurt more than read speed. Every insert, update, and delete has to maintain each index. On a busy product, that cost adds up fast. It's common to save 80 ms on one search and quietly add hundreds of milliseconds to write-heavy jobs.

Pagination hides another trap: the count query. Many search pages run one query to fetch rows and another to count all matches. Teams tune the visible query, ignore the count, and then wonder why the page still feels slow. On broad searches, the count can be the slowest part.

A simple rule helps: tune what users actually do, not what the filter panel suggests. The UI may offer many choices, but traffic usually settles around a few repeated paths. Those paths deserve the index work. The rest can wait until the logs prove they matter.

Checks to run before and after each change

Before you change an index, save a clean baseline. Record response time for the search queries people run most often, not just a test from your laptop. Keep p50 and p95 if you have them, and note how long the slowest common searches take.

A plain before-and-after note prevents a lot of guesswork. If a query drops from 900 ms to 120 ms, that's clear. If one filter gets faster but the rest stay flat, you'll see that too.

Your baseline does not need to be fancy:

• the top search patterns by real traffic
• response time before the change
• query plan before the change
• current index size

After each change, rerun those same patterns. Use the filters and sort orders people actually use in production, such as category plus price sort, status plus newest first, or text search plus date range. One improved query does not mean the page is fixed.

Then confirm that the database picked the plan you expected. Check the actual query plan, not just the SQL text. You want to see that the app uses the new index, avoids extra sorting where possible, and stops scanning far more rows than needed. This matters because a small change in SQL from the app or ORM can push the planner onto a different path.

Do not stop after read speed improves. New indexes add write cost, so watch inserts and updates for a few days. A search page may feel faster while product imports, status changes, or inventory updates quietly slow down in the background.

Storage deserves a look as well. Indexes can grow faster than teams expect, especially on busy tables with several similar filter combinations. If an index saves 30 ms but adds several gigabytes, that trade-off may be poor on a lean setup.

Keep the check simple: faster common searches, acceptable write speed, controlled index growth, and the expected query plan in production. If one of those fails, adjust again instead of stacking on another index.

What to do next

Search pages keep changing after launch. New filters appear, product teams change sort options, and users combine fields in ways nobody expected. If you want indexes to stay useful, treat index review as normal product upkeep instead of a one-time setup.

A simple schedule works better than a huge tuning project. Review search behavior after any release that changes filters, sort order, or result size. Then do a broader check every month or quarter, depending on traffic. That rhythm catches drift early, before slow queries pile up and turn into support issues.

Your logs should help humans, not just machines. Record the filter set, sort choice, result count, query time, and how often each pattern appears. Keep the names plain. "category + price asc" is easier to work with than a raw blob of parameters. When the team can read the patterns at a glance, decisions get faster.

Keep each round of index work small. Pick one or two search patterns that show up often or cost too much. Change the index, measure again, and stop if the gain is tiny. A narrow fix usually beats a full rewrite, especially when the page still changes every few weeks.

A practical routine looks like this:

• review search logs on a fixed schedule
• group real requests into repeated filter and sort patterns
• change indexes only for patterns with enough traffic or clear slowdown
• compare query time and write cost before and after each change

This also helps teams avoid a common trap: adding indexes for edge cases that almost nobody uses. Those extra indexes slow writes and add maintenance, but they do little for real users. Traffic should decide what stays.

If you need a second set of eyes, Oleg Sotnikov at oleg.is works with teams on practical post-launch tuning and can help review production query patterns before you start adding more indexes.

The next review should leave you with a short list, not a giant backlog. Three real query patterns, one clear change, and one fresh measurement is often enough to keep search fast without turning tuning into a side project.