I have a search query in my component that shortly will need to support more than one lookup field where only one would be filled on the certain object. For now the query looks as follows:
SELECT Name, ... FROM Object WHERE First_Field__c =: value [AND... many other conditions]
First_Field__c is a lookup to
I am wondering what would be more reasonable to do considering efficiency and performance of the query.
I can think of two solutions:
1. Add an OR to the query
SELECT Name, ... FROM Object WHERE First_Field__c =: value OR Second_Field__c =: value
Second_Field__c is a lookup to
2. Create a formula field Formula__c like
ISBLANK(First_Field__c, Second_Field__c ,First_Field__c ) and use it in the query without an OR.
SOSL cannot be leveraged in this scenario as I am using multiple cross-reference fields and conditions. As we know, lookup fields are indexed while formulas are not but ORs also slow down queries. It seems there is no optimal solution, is there?