do you have any performance benchmark numbers for this one or is this more speculative?

Yeah, it's kinda of speculative but I can get some data

For the above queries, with db

SELECT COUNT(*) FROM TokenIndexEntity;
74942 rows

SELECT COUNT(*) FROM TokenIndexEntity WHERE resourceType = 'Location';
11784 rows

SELECT COUNT(*) FROM ResourceEntity;
8881 rows

SELECT COUNT(*) FROM ResourceEntity WHERE resourceType = 'Location';
1966 rows

The first query took around 17ms and the second one 7ms

Additional context

SELECT COUNT(*)
FROM (SELECT resourceUuid
      FROM TokenIndexEntity
      WHERE resourceType = 'Location'
        AND index_name = 'status'
        AND index_value = 'active'

      INTERSECT

      SELECT resourceUuid
      FROM TokenIndexEntity
      WHERE resourceType = 'Location'
        AND index_name = 'type'
        AND (index_value = 'bu' AND
             IFNULL(index_system, '') = 'http://terminology.hl7.org/CodeSystem/location-physical-type'));

57 rows

SELECT COUNT(*)
FROM (SELECT resourceUuid
      FROM TokenIndexEntity
      WHERE resourceType = 'Location'
        AND index_name = 'status'
        AND index_value = 'active'

      UNION ALL

      SELECT resourceUuid
      FROM TokenIndexEntity
      WHERE resourceType = 'Location'
        AND index_name = 'type'
        AND (index_value = 'bu' AND
             IFNULL(index_system, '') = 'http://terminology.hl7.org/CodeSystem/location-physical-type'));

2023 rows

I think one limitation that this exposes is that if we use union to join multiple filters, we are more likely to be hit by the limit of values in the IN statement.

But I think it's a problem with our existing queries anyway... not a problem for this PR, but we should perhaps add some more documentation.