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

Heuristics

  1. Primary key: Prefer monotonic keys (auto-increment or time-ordered UUIDs) to reduce split hot spots.
  2. Secondary indexes: Only add when EXPLAIN shows full tablet scans above your row threshold.
  3. Covering indexes: Include filter + 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 index_merge disabled queries; sometimes two weak indexes beat one bloated covering index.