1116 Too many tables; MySQL can only use 61 tables in a join

Problem/Motivation

The core/lib/Drupal/Core/Entity/Sql/SqlContentEntityStorage::loadFromDedicatedTables() generates SQL queries that exceed MySQL's maximum join limit when loading entities with a large number of field storage tables.

When an entity type contains more than 61 field tables, the method attempts to join all field tables into a single query during entity loading. This results in MySQL returning the following fatal error:

SQLSTATE[HY000]: General error: 1116 Too many tables; MySQL can only use 61 tables in a join: SELECT "my_entity".*, "my_entity__field_my_short_str"."field_my_short_str_value" AS "field_my_short_str_value", ...

The issue occurs when visiting entity pages (for example, view or edit forms) as well as Views pages that display the affected entity type. I believe it was introduced here:
#2875033: Optimize joins and table selection in SQL entity query implementation

As Drupal continues to support highly configurable entity types, sites with a large number of fields can hit this database limitation even when the field configuration itself is valid.

Steps to reproduce

• Create one or more content or custom entity types.
• Add more than 61 additional fields to an entity type.
• The fields can be either single-value or multi-value cardinality fields' sets.
• The sets can be dedicated single-value or multi-value fields.

Visit an entity view page, an entity edit form, or a Views page displaying the entity type and observe the fatal error.

The issue occurs on entity types containing at least one set of more than 61 fields.
The issue also occurs on entity types containing multiple groups of sets whose combined storage tables exceed the MySQL join limit.
Both single-value and multi-value field storage configurations are affected.

Proposed resolution

Avoid generating a single query that joins every field storage table when the resulting query would exceed the database engine's join limit.

Query entity data in smaller batches of joins and then merge them. This follows guidance from MySQL documentation and community recommendations:

The only alternative I could suggest is to do the join in pieces, and combine the resulting data in your application.

This would allow Drupal to support entity types with very large field counts without exceeding MySQL's maximum join limit.

Remaining tasks

No.

User interface changes

No.

Introduced terminology

No.

API changes

No.

Data model changes

No.

Release notes snippet

No.