Use INTERSECT and UNION for multiple filters in generated query #2725
Description
A suggestion to use Intersect or Union of the resulting resourceUuid of the index table subqueries depending on the Search operation. This might help reduce redundancy if same resourceUuid appears across the tables or in the case of operation AND, resourceUuid doesn't appear.
Example
Query generated for multiple filters currently uses multiple ANDs across the different index tables
An example to currently generated query
SELECT a.resourceUuid, a.serializedResource FROM ResourceEntity a
WHERE a.resourceType = 'Location'
AND
a.resourceUuid IN (
SELECT resourceUuid FROM TokenIndexEntity WHERE resourceType = 'Location' AND index_name = 'status' AND index_value = 'active')
AND
a.resourceUuid IN (
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'))
May be replaced with
SELECT a.resourceUuid, a.serializedResource
FROM ResourceEntity a
WHERE a.resourceType = 'Location'
AND a.resourceUuid IN (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'));