How to Optimize SOQL Query when I have 15 million Account records?

Question

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.

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.

Answer 1

This is the perfect use case for SOSL, instead. You can do:

FIND :phoneNumber 
IN PHONE FIELDS
RETURNING Account(
  Id 
  WHERE RecordType.Name='Person Account'
  ORDER BY CreatedDate
  LIMIT 1
)

Note: If any of those custom fields are formula fields, this approach will miss those records, as formula fields are not search indexed. You need to actually copy the values down to the record being searched for performance reasons.

This statement would still be true with SOQL, by the way; you need to make the query selective, which means not using formula fields. As long as all the fields filtered are indexed, then your original query should not be a problem, even at 15 million records of data volume.