We have a custom object having more than 10 million records. There was an existing ETL process which fetches 200K records from this object randomly on daily basis. Recently a requirement has come up to introduce an order by clause in the existing query of ETL job so that records are processed based on priority (the priority field is of type checkbox and used in the order by clause). However when we tried using this field in order by clause we are getting QUERY_TIMEOUT exception.
When we reached out to Salesforce Support to create index on that boolean field, they responded that it is not possible to create index for a field in the order by clause they can create index for filter fields only.
I did not understand the reason/justification given by the support team. Is there any other way to address that issue?