compute.percentile API times out after 1.13.1 upgrade due to missing index on entity_usage table

[Prajwal214]

Affected module

Backend

Describe the bug

The endpoint:

POST /api/v1/usage/compute.percentile/{entityType}/{date}

fails with client-side SocketTimeoutException.

The server logs show:

MySQLTransactionRollbackException:
Lock wait timeout exceeded; try restarting the transaction

To Reproduce

This is a regression introduced by PR #25751.

The migration changed the entity_usage index from:

(usageDate, id)

to:

(id, usageDate)

to reduce deadlocks during usage upserts.

This benefits the Usage PUT APIs (which filter by id), but negatively impacts compute.percentile, which filters primarily by:

usageDate
entityType

Since there is no suitable index for this query anymore, MySQL performs a full table scan, causing:

long-running UPDATE queries
client socket timeouts
retries colliding with the original transaction
lock wait timeout exceptions

Expected behavior

Add a dedicated index for percentile computation:

ALTER TABLE entity_usage
ADD INDEX pctl_lookup (usageDate, entityType);

OS

No response

Python version

No response

OpenMetadata version

1.13.1

OpenMetadata Ingestion package version

No response

Additional context

No response

Pre-submission checklist

• I searched for duplicate issues.
• I removed credentials, hostnames, emails, and other sensitive data from logs and config.