CountWorkflowExecutions GROUP BY ExecutionStatus unnecessarily JOINs custom_search_attributes and chasm_search_attributes — 8-16x slower than needed on MySQL

[ricky2129]

Expected Behavior

CountWorkflowExecutions with query=GROUP BY ExecutionStatus should complete in ~1 second on a namespace with ~730K workflows. The query only needs namespace_id and status from executions_visibility — no custom search attribute data is required.

Actual Behavior

The query takes 16–26 seconds on MySQL with ~730K rows, making the Temporal UI completely unresponsive. The UI fires this query on every page load and every60 seconds via the count poller, causing cascading timeouts when multiple users are active

EXPLAIN ANALYZE shows the actual time breakdown:

-> Group aggregate: count(0) (actual time=826..16480 rows=2)
-> Nested loop left join (actual time=0.16..10346 rows=728190)
-> Nested loop left join (actual time=0.183..16409 rows=728190)
-> Covering index scan on ev using by_status (actual time=0.121..909)

Without the JOINs: 1.11 seconds (15x faster).

Confirmed via Temporal CLI (no UI, no browser):
time temporal workflow count --namespace autopay --query "GROUP BY ExecutionStatus"
real: 26.159s

Steps to Reproduce the Problem

1. Set up Temporal server 1.31.1 with MySQL visibility backend
2. Have a namespace with ~700K+ workflow executions (e.g. 10 day retention with steady traffic)
3. Open the Temporal UI workflows page — it will not load (count badge spins indefinitely)
4. Run directly via CLI: time temporal workflow count --namespace --query "GROUP BY ExecutionStatus" - observe 15-30s response time
5. Run the equivalent SQL without JOINs: SELECT status, COUNT(*) FROM executions_visibility WHERE namespace_id = ? GROUP BY status — observe ~1s response time

Root Cause

buildCountStmt in common/persistence/visibility/store/sql/query_converter_legacy_mysql.go unconditionally JOINs both custom_search_attributes and chasm_search_attributes regardless of whether the query references any custom search attribute columns:

return fmt.Sprintf(
SELECT %s FROM executions_visibility ev LEFT JOIN custom_search_attributes USING (%s, %s) LEFT JOIN chasm_search_attributes USING (%s, %s) WHERE %s %s, ...)

For GROUP BY ExecutionStatus with no filter, queryString is empty and ExecutionStatus maps to the status column in executions_visibility directly. Neither joined table contributes to the result. The JOINs should be skipped when queryString does not reference custom attribute column names (Bool0x, Keyword0x, Int0x, Datetime0x, Text0x, KeywordList0x, Temporal*)

Specifications

• Version: Temporal Server 1.31.1
• Platform: Self-hosted on AWS EKS, MySQL 8.4 as the visibility store, cassandra as the datastore , ~730K workflows per namespace, 10d retention

The same issue exists in query_converter.go (lines 181, 223) and the PostgreSQL/SQLite equivalents.