Performance: Taxonomy pages timeout (30s+) with Eloquent Driver when a term has 10k+ entries

[buddy94]

Bug description

When using the Eloquent Driver with a MySQL database that has a large number of entries assigned to a single taxonomy term, taxonomy listing pages time out (HTTP 500) after 30+ seconds.

There are two separate bottlenecks that both use the same inefficient pattern — collecting all matching entry IDs into PHP memory, then sending them back to the database as a massive WHERE IN (...) clause.

Bottleneck 1 — addTaxonomyWheres() in QueriesTaxonomizedEntries trait

The EntryQueryBuilder in the Eloquent driver uses the QueriesTaxonomizedEntries trait from statamic/cms without overriding addTaxonomyWheres(). The Stache-based implementation does:

// Step 1: collect ALL matching entry IDs from the Stache associations index
$entryIds = collect($this->taxonomyWheres)
    ->reduce(function ($ids, $where) {
        $keys = $this->getKeysForTaxonomyWhereBasic($where); // fetches 10k+ IDs
        return $ids ? $ids->intersect($keys)->values() : $keys;
    });

// Step 2: send all IDs back as WHERE IN (...)
$this->whereIn('id', $entryIds->all()); // WHERE id IN ('uuid1','uuid2',...uuid10992)

With 10,992 entries for the "ticino" category: ~30 seconds → timeout.

The Eloquent driver already stores taxonomy data as JSON in the data column. It could apply whereJsonContains directly without ever loading IDs into PHP.

Bottleneck 2 — entriesCount() in TermRepository not overridden

Statamic\Eloquent\Taxonomies\TermRepository extends the Stache TermRepository and does not override entriesCount(). When a $status filter is applied (which happens on every taxonomy page via AugmentedTerm), the Stache implementation does:

public function entriesCount(Term $term, ?string $status = null): int
{
    $items = $this->store->store($term->taxonomyHandle())
        ->index('associations')
        ->items()
        ->where('value', $slug); // loads all associations from Stache

    if ($status) {
        return Entry::query()
            ->whereIn('id', $items->pluck('entry')->all()) // 10k+ IDs again!
            ->whereStatus($status)
            ->count();
    }
    return $items->count();
}

With 10,992 entries for "ticino": ~17 seconds just for this count.

Combined Impact

On a taxonomy page for a term with 10,992 entries:

Operation	Before	After fix
addTaxonomyWheres() paginate query	~30s → timeout	0.06s
entriesCount() via AugmentedTerm	~17s	0.3s
Total page load	HTTP 500	~4s

How to reproduce

1. Set up a Statamic site with the Eloquent driver for entries and terms (MySQL 8+)
2. Import 10,000+ entries, most assigned to a single taxonomy term (e.g., a "region" or "category" term)
3. Visit the taxonomy listing page for that term (e.g., /categorie-news/ticino)
4. Observe 30+ second load time or HTTP 500 timeout

Suggested fix for EntryQueryBuilder (Eloquent driver) — override addTaxonomyWheres() to apply whereJsonContains directly instead of collecting IDs from Stache:

protected function addTaxonomyWheres(): void
{
    foreach ($this->taxonomyWheres as $where) {
        match ($where['type']) {
            'Basic' => $this->applyTaxonomyWhereBasic($where),
            'In'    => $this->applyTaxonomyWhereIn($where),
            'NotIn' => $this->applyTaxonomyWhereNotIn($where),
        };
    }
}

private function applyTaxonomyWhereBasic(array $where): void
{
    [$taxonomyHandle, $slug] = explode('::', $where['value']);
    $taxonomy = Taxonomy::find($taxonomyHandle);
    if (! $taxonomy) {
        $this->where('id', '__impossible__');
        return;
    }
    $collectionHandles = $taxonomy->collections()->map->handle()->all();
    if (! empty($collectionHandles)) {
        $this->whereIn('collection', $collectionHandles);
    }
    $this->builder->whereJsonContains($this->column($taxonomyHandle), $slug);
}

Suggested fix for TermRepository (Eloquent driver) — override entriesCount() to use the Entry query builder instead of Stache associations:

public function entriesCount(Term $term, ?string $status = null): int
{
    $query = Entry::query()
        ->whereTaxonomy($term->taxonomyHandle().'::'.$term->inDefaultLocale()->slug());

    if ($term instanceof LocalizedTerm) {
        $query->where('site', $term->locale());
    }
    if ($collection = $term->collection()) {
        $query->where('collection', $collection->handle());
    }
    if ($status) {
        $query->whereStatus($status);
    }

    return $query->count();
}

Logs

Environment

Application Name .............. Catt
Laravel Version ............... 12.53.0
PHP Version ................... 8.4.17
Composer Version .............. 2.9.4
Environment ................... local
Database ...................... mysql (MySQL 8.0.30)
Statamic Version .............. 6.5.0 PRO
statamic/eloquent-driver ...... 5.3.0

Statamic Eloquent Driver (relevant drivers):
  Entries ..................... eloquent
  Taxonomies .................. eloquent
  Terms ....................... eloquent

Installation

Starter Kit using via CLI

Additional details

• Adding a MySQL multi-valued index on the JSON taxonomy column speeds up whereJsonContains further (~0.01s vs ~0.06s), but is optional:

  ALTER TABLE entries ADD INDEX idx_tags ((CAST(data->'$.tags' AS CHAR(100) ARRAY)));

• This affects all sites using the Eloquent driver once they accumulate thousands of entries per taxonomy term — it is not specific to this use case.
• The entriesCount() issue originates in statamic/cms (Stache\Repositories\TermRepository). The Eloquent driver should override it proactively, as the Stache implementation is not safe for database-backed repositories at scale.

[ryanmitchell]

Unfortunately the fix is not this simple as we offer the option of having split configuration, so we cant safely assume we are entirely eloquent or entirely stache. If you are able to do that, you can override our implementation with your own local one and define that in the eloquent driver config.

The longer term solution is creating sort of pivot tables, which should allow for quicker lookups regardless of storage driver.