Scenarios

At 100 rows per page, the table is 1,000,000 pages. 4% is 4 million rows scattered across pages, so the index path is up to 4 million random page reads at a cost of 4.0 each, 16 million units, against 1 million for a sequential scan. Even on NVMe, with a random cost of 1.1, it is 4.4 million against 1 million. The planner is right to scan.

• Accept the scan, and make it cheaper with columnar storage or partitioning by status.
• Cluster the table by status, if that is the dominant access path.
• Make the filter more selective by combining predicates (status = 'refunded' AND created_at > …) with a composite or bitmap index.
• Use a partial index if only a narrow, recent subset is queried.

Every extra index taxes writes. Drop the status index if no query benefits from it.

Spend the clustered order on the most valuable range scan: (tenant_id, created_at) serves recent orders per tenant and, as a prefix, all orders of a tenant.

A secondary index on (customer_id, created_at). If the primary key is wide in InnoDB, remember every secondary entry carries a copy of it.

A partial index on (tenant_id) WHERE status = 'pending': pending orders are a small, static subset. Confirm the application writes the predicate as a literal, not a parameter.

A time range, best served by clustering or zone maps rather than a new index.

One giant tenant (skew), correlated tenant_id and status throwing off estimates, and indexes that are never used: review scan counts monthly.

Three indexes, not eight.

With an index local to each shard, every query goes to all 12. If each shard is slow 1% of the time, a query meets at least one slow shard with probability 1 − 0.99¹² ≈ 11.4%, so the tail grows with the shard count. Fix: fewer, larger shards; a routing key that sends most queries to one shard; or hedged requests.

New documents are written as small immutable segments that must be opened for search and later merged. The 30 seconds is the refresh interval. Shortening it creates many small segments: more merge work, and more segments per query.

If 1–5 seconds of delay is acceptable, set the refresh to that and watch segment counts and merge load. Where users must see their own writes, read from the primary store by ID.

200 million × 1,024 × 4 bytes = 819 GB raw. That does not fit one normal machine, so the memory cliff decides the architecture.

Product quantization with, say, 128 one-byte sub-codes gives 128 bytes per vector: 25.6 GB, which fits, at a recall cost you must measure.

Add a coarse inverted-file (IVF) layer with about √(N × nprobe) cells: for nprobe = 8, √(1.6 × 10⁹) ≈ 40,000 cells, so each query scans about N × 8 ÷ 40,000 = 40,000 vectors. Or use a graph index (HNSW) if memory allows: 32 links × 4 bytes × 200 million = 25.6 GB for the links alone, plus the compressed vectors.

Recall against memory against build time. Deletes need tombstones and periodic rebuilds. Re-ranking the candidates with full-precision vectors read from disk can recover recall cheaply.

IVF with product quantization on a few nodes first; measure recall against exact search on a sample; then decide.

Proximity in two dimensions: no single sort order preserves it, so you choose which locality to break.

Cells (S2 or H3 at about 1 km) keep ordinary B-tree machinery: a 1 km radius covers a handful of cells, a number of ranges that grows with the perimeter, followed by an exact distance check. An R-tree gives exact containment, but its boxes overlap and the number of subtrees to visit is unpredictable.

A composite key (category, cell_id) if a category is nearly always given; otherwise intersect candidates with a bitmap per category.

It changes constantly: do not index it; filter afterwards.

Dense cities with many places per cell (use adaptive cell levels), hot cells, and skew.

Cells as the key, an exact distance check as refinement, and category as the leading key or as a bitmap.

Cheap, consistent writes, but every email search fans out to all N shards: with N = 32, 1 − 0.99³² ≈ 27.5% of searches meet a slow shard, and cost grows with N.

Searches hit one shard, but every user write touches two shards and needs a distributed transaction (two-phase commit): higher write latency and more ways to fail; a stuck coordinator blocks writes.

One-shard searches and cheap writes, but results are stale or missing for seconds after a change, and a uniqueness rule on email cannot be enforced reliably.

If email must be unique and strongly consistent, as at sign-up, keep a dedicated mapping table whose single row per email is the authority, written first, with the uniqueness check, inside one shard. Use asynchronous global or local indexes for search features that tolerate delay. The consistency boundary can be moved, never removed.

There is no B-tree here: the main index is the min/max statistics per row group and per file. So the question is not "is there an index?" but "how does the filtered column relate to the file layout?"

• Is the table sorted or Z-ordered on the filtered column?
• Did late-arriving data, or many small files, break the clustering?
• Do the row groups' min/max ranges overlap? Inspect the footer statistics.
• Can the predicate be pushed down at all? A function wrapped around the column prevents it.

Rewrite with the right sort key or clustering, compact small files, and stop wrapping filtered columns in functions.

Reclustering costs write work and compute, so do it for the columns that appear in most queries.

Min/max statistics prune only if values follow the physical order. Written in arrival order, every file's customer_id range spans nearly the whole domain, so nothing is pruned. Measure the average range width as a fraction of the domain.

Sort or cluster by customer_id during compaction (or Z-order on several columns), which narrows the ranges. Add a per-file Bloom filter for equality on this high-cardinality column. Look for sentinel values (null stored as 0, or 9999-12-31) and replace them with real nulls.

Reading 200,000 file entries on every query makes planning cost grow with the file count: add a hierarchy of manifests and compact small files.