The use whereTaxonomy method makes a really big check inside a query which makes the query take very long.

[SuperHydroman]

Bug description

Hi, another question regarding statamic performance.

I have a custom query scope, being used in the CP as well as in my templates

$query->whereTaxonomy('resource_type::customer-cases')
    ->where('status', 'published')
    ->whereNotNull('logo')
    ->whereNotNull('logo_asset_exists');

I have noticed that the ->whereTaxonomy() method, adds a check against all entries from a given collection/taxonomy (not really sure), I've attached a screenshot in where you can see the query that is being run. This makes the query very big and takes a while to complete. Having multiple checks like this on a single page makes the load time increase significantly for obvious reasons.

I want to ask why this adds so much to the query and how there is a better method of doing this.

FYI: The big query is WITH the ->whereTaxonomy() method and the second (small query) is WITHOUT the ->whereTaxonomy() method.

How to reproduce

1. Make a collection
2. Make a taxonomy, in which you make some entries
3. Add a taxonomy select to the collection
4. Link a taxonomy to the collection
5. Try to retrieve only a specific set of entries from the collection using the whereTaxonomy method. (See snippet above)
6. Enable Laravel DEBUGBAR and see the big query (large if you have a lot of entries inside the collection).

Environment

Environment
Application Name: Classified
Laravel Version: 11.44.2
PHP Version: 8.3.19
Composer Version: 2.8.6
Environment: local
Debug Mode: ENABLED
URL: ultimo-statamic.test
Maintenance Mode: OFF
Timezone: Europe/Amsterdam
Locale: en

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

Drivers
Broadcasting: log
Cache: file
Database: mysql
Logs: stack / single
Mail: log
Queue: sync
Session: file

Statamic
Addons: 2
Sites: 6 (English, Deutsch, Español, and 3 more)
Stache Watcher: Enabled
Static Caching: Disabled
Version: 5.52.0 PRO

Statamic Addons
thoughtco/statamic-cache-tracker: 0.9.3
withcandour/aardvark-seo: 5.0.2

Installation

On-going project, created from Statamic version 5.28.0, with the use of our custom Starter kit, upgraded in the meantime multiple times till the latest version, speaking as of now. Project has been on-going for 5-6 months. Now that the website is live, we noticed the extremely slow loading speeds, which made us investigate, stumbling upon this bug.

[duncanmcclean]

In your particular case, you might be able to swap out ->whereTaxonomy() for ->whereJsonContains():

- $query->whereTaxonomy('resource_type::customer-cases')
+ $query->whereJsonContains('resource_types', 'customer-cases')
    ->where('status', 'published')
    ->whereNotNull('logo')
    ->whereNotNull('logo_asset_exists');

[SuperHydroman]

Hey Duncan, sorry for the late response. Quite busy at work haha, I think I've actually tried that and didn't work, therefore using a default where clause now, which works and has a smaller query being generated.