The recorded reduction from 1,922 rows read to 64 is useful only with its other half: the two organization searches still returned the same eight and three results.

The work replaced whole-article string comparisons with existing full-text search and an organization index. It narrowed the path to the answer rather than simply discarding results. These measurements describe that September workload, not a universal performance promise.

I preserve the visitor's answer first

I do not want a necessary result to disappear merely to reduce cost. Keeping the answer while doing less work and narrowing the information offered are different decisions. I explain which one I made.

Freeze the answer before changing the query

  1. Fix the search inputs and dataset point in time.
  2. Record result IDs, ordering, pagination and null handling.
  3. Compare rows read, CPU and output under the same conditions.
  4. Include empty results, broad queries, languages and date boundaries.

Read the plan using a small synthetic schema

This is a synthetic example, not the production database. It illustrates evaluating a compound index for a known filter and ordering. Balance read savings against write and storage overhead.

Explore the implementation

Synthetic SQLite/D1 example · adapt to your schema

CREATE TABLE notes (
  id INTEGER PRIMARY KEY,
  organization TEXT NOT NULL,
  published_at TEXT NOT NULL,
  title TEXT NOT NULL
);
CREATE INDEX idx_notes_org_date
  ON notes(organization, published_at DESC, id DESC);

EXPLAIN QUERY PLAN
SELECT id, title, published_at FROM notes
WHERE organization = 'Example Institute'
ORDER BY published_at DESC, id DESC
LIMIT 20;

A better plan still needs a result comparison

An existing index is not proof that a particular query uses it. Compare the predicates and ordering with the index, inspect the plan, then examine real read metrics. Leading-wildcard searches need separate consideration.

Selecting only necessary columns, separating list and detail work, and moving schema changes out of the request path are other candidates. Begin with frequent expensive queries, and disclose changes that alter the result's meaning.

What I record

Keep dataset conditions, before/after queries, plans, result comparisons and observation windows together. Distinguish equivalent-result optimization from reducing the feature's scope.