On wide OceanBase tables, each secondary index adds write amplification because indexes are separate tablets. Add indexes only when EXPLAIN shows costly scans, prefer covering indexes for hot queries, and rebuild after bulk loads.

Wide tables with dozens of columns invite too many secondary indexes. OceanBase stores indexes as separate tablets; each index adds write cost on INSERT and UPDATE.

Heuristics

  1. Primary key: Prefer monotonic keys (auto-increment or time-ordered UUIDs) to reduce split hot spots.
  2. Secondary indexes: Add only when EXPLAIN shows full tablet scans above your row threshold.
  3. Covering indexes: Include filter and projection columns to avoid back-table lookups.

Example

For WHERE store_id = ? AND status = 'open' ORDER BY created_at DESC LIMIT 50:

CREATE INDEX idx_store_status_created
  ON orders (store_id, status, created_at DESC);

Verify with EXPLAIN ANALYZE. Expect index range scan, not hash join on a filter table.

Maintenance

Rebuild indexes after bulk loads. Monitor queries where index merge is disabled; sometimes two selective indexes beat one bloated covering index.

Validation

Compare p95 latency and CPU before and after index changes on staging with production-shaped data volume.

Sources and further reading