How to fix "QUERY_TIMEOUT: Your query request was running for too long." error

Question

I have a batch class with the below start method and its hitting error QUERY_TIMEOUT: Your query request was running for too long. The conditions added in the query are required. Is there a way to fix or optimize this query?

    public Iterable<SObject> start(Database.BatchableContext context) {
    List<Training__c> trainings = [
            SELECT Status__c,
                    Type__c,
                    Module_Type__c,
                    Scheduled_Date__c, future_Schedule_date__c,future_Schedule_date2__c

            FROM Training__c
            WHERE Type__c = 'N/A'
            AND Scheduled_Date__c = NULL
            AND is_voc__c = TRUE
            AND VTD1_Status__c IN ('Future Training')
            AND (future_Schedule_date = TODAY OR future_Schedule_date2 = TODAY)
    ];
    return trainings ;
}

Answer 1

I don't know how many of those fields are indexed, but there is another reason for your query to take that long: too many records to be returned by the database.

In this case you should look into using a query locator instead of trying to query all records at once. With a query locator you'll be able to query millions of records in smaller chunks.

From the Apex Developer Guide on "Using Batch Apex" documentation:

When you’re using a simple query (SELECT) to generate the scope of objects in the batch job, use the Database.QueryLocator object. If you use a QueryLocator object, the governor limit for the total number of records retrieved by SOQL queries is bypassed. For example, a batch Apex job for the Account object can return a QueryLocator for all account records (up to 50 million records) in an org.

Check the "Batch Apex Examples" section. The first example uses a query locator.

Answer 2

The query can be optimized using the SOQL query optimization mechanisms described e.g. in this Trailhead: https://trailhead.salesforce.com/content/learn/modules/database_basics_dotnet/writing_efficient_queries

Some first thoughts:

1. replace AND VTD1_Status__c IN ('Future Training') by AND VTD1_Status__c = 'Future Training'
2. if there are any text fields among the filters, make those text fields 'external ID' (as described here: https://help.salesforce.com/s/articleView?id=000320036&type=1)

Based on the amount of records in the table more advanced mechanisms could be needed, like https://blog.bessereau.eu/assets/pdfs/salesforce_large_data_volumes_bp.pdf