Index not getting picked up while running worfklow api which returns running workflows

[raftar200197]

Summary

Temporal's MySQL standard visibility silently depends on the MySQL optimizer switch
prefer_ordering_index=on. With it set to off, ListWorkflowExecutions degrades from
~3 ms to 20–25 minutes, because the optimizer rejects Temporal's own
by_temporal_namespace_division index and falls back to a full namespace scan plus filesort.

This dependency is not documented and there is no startup warning. It caused three
production CPU saturation events in ~30 hours.

Versions

Component	Version
Temporal Server	1.28.0
Helm chart	temporalio/helm-charts → temporal 0.64.0 (appVersion 1.28.0)
Temporal UI	2.38.3
MySQL	8.0.32 MySQL Community Server - GPL (InnoDB 8.0.32)

Platform

• AWS EKS, ap-south-1 — deployed via ArgoCD
• MySQL: self-managed on EC2 (not RDS), 4 vCPU / 30.8 GiB RAM
• Service replicas: frontend 5, history 5, matching 5, worker 5

Temporal persistence configuration

server:
  config:
    numHistoryShards: 512
    persistence:
      defaultStore: default
      default:
        driver: "sql"
        sql:
          driver: "mysql8"
          database: "temporal_v28"
          maxConns: 20
          maxIdleConns: 20
          maxConnLifetime: "1h"
      visibility:
        driver: "sql"
        sql:
          driver: "mysql8"
          database: "temporal_visibility_v28"   # same MySQL host as default store
          maxConns: 20
          maxIdleConns: 20
          maxConnLifetime: "1h"

elasticsearch:
  enabled: false        # standard SQL visibility

14 namespaces; retention 100d ×11, 90d ×1, 30d ×1, 730d ×1 (temporal-system).

MySQL server configuration

The two non-default optimizer settings:

optimizer_switch:  prefer_ordering_index=off      <-- default is ON; this is the cause
                   index_merge_intersection=off   <-- default is ON (unrelated)

Full relevant config:

version                              = 8.0.32
max_execution_time                   = 0
innodb_buffer_pool_size              = 20669530112 (19.25 GiB)
innodb_buffer_pool_instances         = 1
innodb_page_size                     = 16384
max_connections                      = 2600
sort_buffer_size                     = 262144
join_buffer_size                     = 262144
tmp_table_size                       = 16777216
table_open_cache                     = 6000
innodb_read_io_threads               = 2
innodb_write_io_threads              = 2
innodb_io_capacity                   = 4000
innodb_flush_log_at_trx_commit       = 1
sync_binlog                          = 1
innodb_lock_wait_timeout             = 50
lock_wait_timeout                    = 31536000
innodb_stats_persistent_sample_pages = 500
long_query_time                      = 1
charset/collation                    = utf8mb4 / utf8mb4_0900_ai_ci

Data profile

• executions_visibility: 13.8M rows (206.6 GB schema)
• Affected namespace alone: ~6.9M rows
• custom_search_attributes: 13.9M rows
• Indexes on executions_visibility: 32 (26 from Temporal's schema, 6 added locally)
• default_idx and by_temporal_namespace_division both present and correct

The query (generated by Temporal)

SELECT ev.namespace_id, ev.run_id, ev.workflow_type_name, ... ev._version
FROM executions_visibility ev
LEFT JOIN custom_search_attributes USING (namespace_id, run_id)
WHERE namespace_id = '<uuid>' AND TemporalNamespaceDivision is null
ORDER BY coalesce(close_time, cast('9999-12-31 23:59:59' as datetime)) DESC,
         start_time DESC, run_id
LIMIT 100

Evidence

Plan chosen by the optimizer (prefer_ordering_index=off):

key: PRIMARY          key_len: 256      ref: const
rows: 6912123         filtered: 10.00
Extra: Using where; Using filesort

-> Limit: 100 (cost=940044.41 rows=100)
  -> Nested loop left join (cost=940044.41 rows=6913370)
    -> Sort: coalesce (cost=75873.15 rows=6913370)     <-- sorts 6.9M rows
      -> Filter (cost=75873.15 rows=6913370)
        -> Index lookup on ev using PRIMARY (rows=6913370)

Same query with FORCE INDEX (by_temporal_namespace_division) — EXPLAIN ANALYZE:

-> Limit: 100 row(s) (cost=3456088.03 rows=100) (actual time=0.088..2.784 rows=100 loops=1)
-> Nested loop left join (actual time=0.088..2.773 rows=100 loops=1)
-> Filter: ((ev.namespace_id = '') and (ev.TemporalNamespaceDivision is null))
(cost=1036826.43 rows=6912176) (actual time=0.062..1.433 rows=100 loops=1)
-> Index lookup on ev using by_temporal_namespace_division
(namespace_id='', TemporalNamespaceDivision=NULL)
(cost=1036826.43 rows=6912176) (actual time=0.061..1.383 rows=100 loops=1)
-> Single-row covering index lookup on custom_search_attributes using PRIMARY
(actual time=0.013..0.013 rows=1 loops=100)

**No `Sort` node. `actual rows=100`, not 6.9M. 2.784 ms total.**

The optimizer estimates `rows=6912176` / `cost=1036826` for the index lookup, because it does
not credit early termination from `LIMIT 100` when the index already supplies the ordering.
It therefore costs the correct plan at 3,456,088 vs 940,044 for the filesort plan — and picks
the plan that is ~500,000× slower.

`ANALYZE TABLE` / histograms do not help, since this is a cost-model issue rather than a
statistics issue.

## Production impact

Three CPU saturation events in ~30 hours on the 4 vCPU instance:
- CPU pinned at 100% (user ~95%, iowait ~0)
- `Threads_running`: 5 → **620**
- `load1`: **246.9** on 4 cores
- `Sort_rows`: 7/s baseline → **65,465/s**
- `executions_visibility` table I/O: 0.04 → **225 thread-s/s**
- Downstream: workflow-service `DualClient` timeouts, gRPC UNKNOWN → HTTP 500 for end users

## Suggested fixes

1. **Document** that MySQL standard visibility requires `optimizer_switch=prefer_ordering_index=on`.
2. **Emit a startup warning** if the switch is `off` — cheaply detectable via `SELECT @@optimizer_switch`.
3. **Add an optimizer hint** to the generated SQL, e.g. `/*+ INDEX(ev by_temporal_namespace_division) */`
   or `FORCE INDEX`, so correctness of the plan does not depend on a server-level setting an
   operator may change for unrelated reasons.