Slow authorization code lookup on MySQL with large oauth2_authorization table

[itanxiao]

Description

We are experiencing a significant performance issue when querying an authorization by authorization_code_value on MySQL.

A single query can take around 17 seconds, and the MySQL slow query log shows that more than 1.5 million rows are examined for a query that returns only one row.

It appears that the lookup on authorization_code_value results in a full table scan because there is no index available for this column.

Current Behavior

The following is a sanitized MySQL slow query log from our production environment:

# Time: 2026-08-13T09:00:30.923552 CST
# User@Host: <REDACTED_USER>[<REDACTED_USER>] @ [<REDACTED_IP>]
# Query_time: 17
# Lock_time: 0
# Rows_sent: 1
# Rows_examined: 1535391

SELECT
    a1_0.id,
    a1_0.access_token_expires_at,
    a1_0.access_token_id,
    a1_0.access_token_issued_at,
    a1_0.access_token_metadata,
    a1_0.access_token_scopes,
    a1_0.access_token_type,
    a1_0.access_token_value,
    a1_0.attributes,
    a1_0.authorization_code_expires_at,
    a1_0.authorization_code_id,
    a1_0.authorization_code_issued_at,
    a1_0.authorization_code_metadata,
    a1_0.authorization_code_value,
    a1_0.authorization_grant_type,
    a1_0.authorized_scopes,
    a1_0.oidc_id_token_expires_at,
    a1_0.oidc_id_token_id,
    a1_0.oidc_id_token_issued_at,
    a1_0.oidc_id_token_metadata,
    a1_0.oidc_id_token_value,
    a1_0.principal_name,
    a1_0.refresh_token_expires_at,
    a1_0.refresh_token_id,
    a1_0.refresh_token_issued_at,
    a1_0.refresh_token_metadata,
    a1_0.refresh_token_value,
    a1_0.registered_client_id,
    a1_0.state,
    a1_0.state_id
FROM oauth2_authorization a1_0
WHERE a1_0.authorization_code_value = '<REDACTED_AUTHORIZATION_CODE>'
LIMIT 2;

This query examined 1,535,391 rows and took approximately 17 seconds, while returning only one row.

The same issue could potentially affect lookups by other token value columns, for example:

authorization_code_value
access_token_value
refresh_token_value
oidc_id_token_value

Expected Behavior

Looking up an authorization by an authorization code or token should remain efficient as the number of authorization records grows.

Ideally, the default database schema or documentation should provide an indexing strategy for columns that are used by token lookup queries.

Database

MySQL / InnoDB

The relevant column in our current schema is:

authorization_code_value BLOB NULL

There is currently an index on:

authorization_code_id

but no index on:

authorization_code_value

Since the query filters by authorization_code_value, the existing authorization_code_id index does not help with this lookup.

Questions

Is this expected behavior for the current database schema?

Would it make sense for Spring Authorization Server to provide or recommend indexes for token lookup columns such as:

authorization_code_value
access_token_value
refresh_token_value
oidc_id_token_value

For MySQL, since these columns are stored as BLOB, a prefix index or an additional hash column may be required.

It would be helpful if the project could provide a recommended indexing strategy for production deployments with large authorization tables.