Alert__c object has a Rule ID field, which is just text, and a lookup to the
Master__c object. I have a trigger on
Alert__c, which when runs after insert, update or delete. It collects the Master ids off all the triggering Alerts, then runs an aggregate query to count certain kinds of Alert that are attached to the Master, and stores those values in the Master object. Basically I need to know how many 183's there were on this Master in the last 7 days, how many 184's, 185's and 186's. I perform the following query, which worked until the Alert__c table got too big:
list<AggregateResult> ars = [select Master__c m, RULE_ID__c RuleId, grouping(RULE_ID__c) grpruleid, count(Id) cnt from Alert__c where Created_Within_Last_7_Days__c = 1 AND (RULE_ID__c='183' OR RULE_ID__c='184' OR RULE_ID__c='185' OR RULE_ID__c='186') group by rollup(Resident__c, RULE_ID__c) having Master__c IN :MasterIds];
Now I receive this error:
Apex script unhandled trigger exception by user/organization: 00530000004s2HV/00D30000001H6e6 MdsAlertsRollUp: execution of AfterInsert caused by: System.QueryException: Non-selective query against large object type (more than 100000 rows). Consider an indexed filter or contact salesforce.com about custom indexing. Even if a field is indexed a filter might still not be selective when: 1. The filter value includes null (for instance binding with a list that contains null) 2. Data skew exists whereby the number of matching rows is very large (for instance, filtering for a particular foreign key value that occurs many times) Trigger.MdsAlertsRollUp: line 22, column 1
Created_Within_Last_7_Days__c is a formula field based on CreatedDate, and Rule_ID__c is configured as an External Id field. For 200 Master records, there aren't typically more than 300 Alert records.
Complicating this is that I can run this query in an Anonymous Apex window successfully, but not within a trigger.
Any ideas? For solution as well as why this is happening?