What is Selective Query in Salesforce

 In Salesforce SOQL, a Selective Query is a query that efficiently uses indexes to return a small subset of records, avoiding full table scans and staying within governor limits (especially the 100,000-row limit).

✅ What Is a Selective Query?

A selective query is one that:

• Uses indexed fields in the WHERE clause

• Returns a small result set

• Avoids performance issues or timeouts during execution

🚫 Non-Selective Query (Bad Example)

sql
SELECT Id, Name FROM Contact WHERE FirstName LIKE '%John%'

• %John% disables index usage

• Can trigger Too many query rows: 100001

---

✅ Selective Query (Good Example)

sql
SELECT Id, Name FROM Contact WHERE AccountId = '001ABC123456789' LIMIT 100

• AccountId is an indexed field

• Returns limited records

🔍 Selectivity Rule of Thumb

For standard or custom objects:

• For a query to be selective, it should filter on an indexed field and return:

  • < 10% of records (if > 1 million records)

  • < 100,000 total rows returned

📌 Indexed Fields (Default)

Type	Examples
Standard	Id, Name, OwnerId, CreatedDate
Custom	Fields marked as External ID or Unique
System-created	RecordTypeId, MasterDetailId, LookupId

You can also request a custom index from Salesforce Support.

🛠️ Tools to Analyze Query Selectivity

1. Query Plan Tool in Developer Console:

   • Shows if the query is selective

   • Use: Query Plan tab after pasting a SOQL query

2. Explain Plan in Workbench:

   • Go to Utilities → Query Plan

   • Paste SOQL to check cost & cardinality

🧠 Tips to Make Queries Selective

• Use indexed fields in filters

• Use = or IN operators (not !=, NOT IN, or LIKE '%abc')

• Avoid filtering on formula fields (not indexable)

• Use skinny tables or custom indexes for reporting-heavy orgs

• Always bulkify Apex logic around SOQL

🧪 Example: Bulk-Safe + Selective

apex
// Query using indexed field and limiting fields fetched
List<Case> cases = [
  SELECT Id, Status, Subject 
  FROM Case 
  WHERE AccountId IN :accountIds
  AND CreatedDate = THIS_MONTH
  LIMIT 500
];