I have a use case where I am exposing a custom Rest service that would accept phone# as an argument and compare with the five phone# fields on Account and would return the single record matching either of the five phone#. In case, if multiple records are found, the earliest created record should be returned. My only concern/problem is when this goes to production, it won't work/there will be a performance issue as there are 15 million Account records. I can probably index the fields, but will that really help when record count is so high? I would really appreciate any suggestions/help to optimize this SOQL.
[SELECT Id FROM Account WHERE RecordType=’Person Account’ AND (Parent_Phone_Number__c= :phoneNo OR Student_Phone_Number__c= :phoneNo OR Alternate_Number_1__c= :phoneNo OR Alternate_Number_2__c= :phoneNo OR Alternate_Number_3__c= :phoneNo) ORDER BY CreatedDate DESC LIMIT 1];
PS: I already asked the Biz team if we can have only one phone# field while comparison. But they are adamant with five fields.