ApexAdvancedsoqlperformanceselectivity
Implement selective SOQL for multi-select picklist and semi-join queries
Real World Scenario
Report-style Apex query filters Accounts where Region__c INCLUDES ('North','South') on 3M Account table times out without index.
Expected Answer
• Multi-select INCLUDES may not use standard indexes—verify in Query Plan
• Junction object or normalized Region_Assignment__c for selective filtering
• Semi-join subquery: Id IN (SELECT AccountId FROM Region_Assignment__c WHERE...)
• Avoid negation operators NOT IN on large tables without selective leading filter
• Pre-compute searchable flags in nightly batch for complex multi-filter UI
• SOSL for fuzzy search; SOQL for exact selective filters only
• Custom index on junction object foreign keys
Follow-Up Questions & Answers
Click to expand — each follow-up includes a direct, interview-ready answer
Main difference: use case and scale. Multi-select INCLUDES may not use standard indexes—verify in Query Plan. Junction object or normalized Region_Assignment__c for selective filtering. Pick based on your integration pattern and team capability. Clever SOQL on multi-select at LDV fails—normalize to junction when filter is hot path. Optimize for scale and operational observability.
Architect Perspective
Clever SOQL on multi-select at LDV fails—normalize to junction when filter is hot path.