CP collection listing becomes effectively unusable with many terms when using the Eloquent Driver

[buddy94]

Bug description

When using Statamic with statamic/eloquent-driver, opening a large collection in the Control Panel can become extremely slow or time out completely.

In our case, the problem happens on /cp/collections/news. The slowdown is caused by taxonomy fields rendered in the listing. During entry serialization, the Eloquent driver executes an expensive correlated subquery for each taxonomy that uses JSON_CONTAINS against the entries.data JSON column.

The issue scales especially badly with a high number of terms. In practice, the number of tags is the dominant factor.

Observed results from our tests:

• On a local machine with a large dataset but a relatively small number of tags, the CP is slow but still usable.
• On a smaller server dataset with a very large number of tags, the tags query alone can exceed one minute, which is enough to trigger request timeouts.
• After increasing the number of entries on that same server, the query becomes even slower.
• In our production context, we have roughly 15,000 tags and 150,000 news entries, and navigating the CP becomes effectively impossible.

The problematic SQL shape is:

SELECT slug FROM taxonomy_terms
WHERE taxonomy = 'tags'
AND EXISTS (
  SELECT 1 FROM entries
  WHERE collection IN ('news')
  AND JSON_CONTAINS(data, CONCAT('"', taxonomy_terms.slug, '"'), '$.tags')
)

From tracing the listing flow, this is triggered while rendering Terms fields in the CP listing, via Terms::queryBuilder() and TermQueryBuilder::applyCollectionAndTaxonomyWheres().

How to reproduce

1. Use Statamic 6 with statamic/eloquent-driver for entries, taxonomies, and terms.
2. Create a collection with a large number of entries, for example news.
3. Add one or more taxonomy-backed Terms fields to the collection blueprint, for example tags and categorie_news.
4. Populate the site with many terms, especially tags.
5. Open /cp/collections/news.
6. Observe that every listing request becomes very slow, and with enough terms it will exceed the request timeout.

Concrete reproduction from our environment:

1. Dev server with 2,762 news entries and 11,833 tags.
2. Open /cp/collections/news.
3. The tags taxonomy query takes about 65.6s.
4. PHP-FPM has request_terminate_timeout = 60, so the request is terminated.

Logs

Environment

Full output of `php please support:details` from the dev server:


Environment
Laravel Version: 13.2.0
PHP Version: 8.4.18
Composer Version: 2.9.5
Environment: production
Debug Mode: OFF
Maintenance Mode: OFF
Timezone: UTC
Locale: fr

Cache
Config: NOT CACHED
Events: NOT CACHED
Routes: NOT CACHED
Views: CACHED

Drivers
Broadcasting: log
Cache: redis
Database: mysql
Logs: stack / single
Mail: smtp
Queue: redis
Scout: collection
Session: file

Storage
public/storage: LINKED

Pulse
Enabled: ENABLED
Version: v1.7.2

Livewire
Livewire: v4.2.2

Statamic
Addons: 11
Sites: 1
Stache Watcher: Disabled (auto)
Static Caching: half
Version: 6.8.0 PRO

Statamic Addons
ancom/admin-toolbar: dev-main
ancom/analytics-dashboard: dev-dev
ancom/homepage-editor: dev-dev
ancom/newsletter-manager: dev-dev
ancom/tutorial: dev-dev
cboxdk/statamic-mcp: 2.0.2
statamic/eloquent-driver: 5.5.0
statamic/importer: 2.1.0
studio1902/statamic-peak-commands: 10.6.0
studio1902/statamic-peak-seo: 11.1.0
studio1902/statamic-peak-tools: 9.0.2

Statamic Eloquent Driver
Addon Settings: file
Asset Containers: eloquent
Assets: eloquent
Blueprints: file
Collection Trees: file
Collections: file
Entries: eloquent
Fieldsets: file
Form Submissions: file
Forms: file
Global Sets: file
Global Variables: file
Navigation Trees: eloquent
Navigations: eloquent
Revisions: file
Sites: file
Taxonomies: eloquent
Terms: eloquent
Tokens: file

Installation

Fresh statamic/statamic site via CLI

Additional details

The issue appears tied to TermQueryBuilder::applyCollectionAndTaxonomyWheres() performing a collection-scoped EXISTS query even when the listing is already resolving specific term ids from the entry itself.

• In our measurements, the number of terms is more important than the number of entries. A smaller dataset with 11,833 tags was much worse than a larger local dataset with only 390 tags.
• On the dev server, the CP timeout is deterministic because the taxonomy query time is already greater than request_terminate_timeout = 60.
• This makes the CP effectively non-navigable for large editorial datasets such as our production setup with about 15,000 tags and 150,000 news entries.

Proposed Resolution

I think the collection-scoped EXISTS query in TermQueryBuilder::applyCollectionAndTaxonomyWheres() should be skipped when the term query is already constrained to specific term ids or slugs coming from the current entry.

In the CP listing context, the Terms field is not trying to discover all terms used by a collection. It is resolving the already-known terms stored on the entry being rendered. In that case, running an additional collection-scoped taxonomy existence query appears redundant and becomes extremely expensive on large editorial datasets.

A possible fix would be:

1. Detect when the builder is already constrained to explicit terms.
2. In that case, avoid the collection-scoped EXISTS subquery.
3. Keep only the taxonomy constraint that is still needed for correctness.

At a high level, something like this:

if ($queryAlreadyTargetsSpecificTerms) {
  // Keep taxonomy filtering if needed.
  // Skip the expensive collection-scoped EXISTS query.
}

[ryanmitchell]

It seems no matter what we do to make this query more performant there's always a use case it doesn't work for... I feel like I'm on repeat but we really wont solve this fully without pivot tables, everything else is a band-aid.

If you make your terms field listable: hidden does that make any difference to your listing load times? It should mean it doesnt try to load the terms for every entry on the listing view.