SOSL vs SOQL, Search Tuning, and Skinny Tables

Salesforce gives you two solid ways to find data: SOQL for precise, table-style queries, and SOSL for full-text, cross-object search. Layer on smart search tuning (filters, wildcards, snippets) to surface relevant results quickly. And for read-heavy orgs, Skinny Tables can help — but they have trade-offs and should be used thoughtfully.

---

SOQL — targeted, relational queries

Big idea:
SOQL works like SQL’s SELECT. It shines when you know exactly which object and fields you’re after and can filter selectively (indexed fields, equality/IN, sargable predicates). It’s perfect for list views, reports, parent/child joins, and precise automation.

Real-world example:
Build a sales dashboard list of recent premium accounts — fast, selective filters, stable sort.

public with sharing class AccountRepo {
    // Selective SOQL: indexed fields + bind variables + LIMIT
    public static List<Account> recentPremium(Integer days) {
        Date since = Date.today().addDays(-days);
        return [
            SELECT Id, Name, Industry, CreatedDate
            FROM Account
            WHERE Industry = : 'Technology'     // picklist equality (selective)
              AND CreatedDate >= : since        // indexed datetime
              AND IsDeleted = false
            ORDER BY CreatedDate DESC
            LIMIT 200
        ];
    }
}

Tips: Use bind variables (avoid injection), filter on indexed fields (Id, OwnerId, CreatedDate, External Ids, etc.), and avoid wrapping columns in functions (e.g., CALENDAR_YEAR(CreatedDate) in WHERE).

---

SOSL — full-text, multi-object search

Big idea:
SOSL is your search engine. One query hits multiple objects and fields using the search index. It’s ideal for global search bars, type-ahead, and “I don’t know which object it’s on” moments.

Real-world example:
A support agent types “Acme renewal” and wants relevant Accounts, Contacts, and Opportunities with context snippets.

public with sharing class GlobalFinder {
    public class Hit { public String typeName; public Id id; public String name; public String snippet; }

    public static List<Hit> searchAll(String q) {
        // Use prefix wildcard (term*) and request snippets/highlighting
        String term = q + '*';
        List<List<SObject>> buckets = [
            FIND :term IN ALL FIELDS
            RETURNING
                Account(Id, Name, Industry ORDER BY Name LIMIT 5),
                Contact(Id, Name, Email ORDER BY Name LIMIT 5),
                Opportunity(Id, Name, StageName ORDER BY LastModifiedDate DESC LIMIT 5)
            WITH SNIPPETS
            LIMIT 15
        ];

        List<Hit> hits = new List<Hit>();
        for (SObject a : buckets[0]) hits.add(new Hit('Account', a.Id, (String)a.get('Name'), (String)a.getSnippet()));
        for (SObject c : buckets[1]) hits.add(new Hit('Contact', c.Id, (String)c.get('Name'), (String)c.getSnippet()));
        for (SObject o : buckets[2]) hits.add(new Hit('Opportunity', o.Id, (String)o.get('Name'), (String)o.getSnippet()));
        return hits;
    }
}

Tips: use RETURNING to cap per-object limits; prefer prefix wildcards (term*), not *term*; WITH SNIPPETS (and highlighting) improves UX.

---

Search tuning — relevance, speed, and UX

Big idea:
Tune both SOQL and SOSL for selectivity and relevance: the right filters, sensible wildcards, strong ordering, and lean payloads. Curate search layouts/fields, cache hot searches, and avoid big-page offset pagination.

Real-world example:
Implement type-ahead that queries Accounts by name prefix when the user types ≥2 chars; if the query gets more complex, fall back to SOSL.

public with sharing class TypeaheadService {
    public static List<Account> fastNamePrefix(String prefix) {
        if (String.isBlank(prefix) || prefix.length() < 2) return new List<Account>();
        // SOQL best-case: left-anchored LIKE with index; limit return fields
        String likeExpr = prefix + '%';
        return [
            SELECT Id, Name
            FROM Account
            WHERE Name LIKE : likeExpr
            ORDER BY Name
            LIMIT 10
        ];
    }

    public static List<List<SObject>> fallbackAnyField(String q) {
        if (String.isBlank(q)) return new List<List<SObject>>();
        String term = q + '*';
        return [
            FIND :term IN ALL FIELDS
            RETURNING Account(Id, Name LIMIT 10)
        ];
    }
}

Tuning checklist:

• SOQL: equality/IN on indexed fields, low-cardinality filters first, LIMIT, selective WHERE; avoid large OFFSET.

• SOSL: prefer prefix wildcards, cap per-object results in RETURNING, use WITH SNIPPETS.

• UX: show top N fast; lazy-load details; debounce keystrokes; cache recent queries (Platform Cache).

---

Skinny Tables — performance boost with trade-offs

Big idea:
Skinny Tables are Salesforce-managed tables that copy a subset of frequently used fields from a standard/custom object, reducing row width and joins. They can speed up read-heavy queries and list views on very wide objects.

Real-world example:
A back-office list view crawls on a 500-field custom object. A skinny table with ~20 high-value fields makes that view snappy.

Trade-offs & cautions (high level):

• Requested and maintained by Salesforce (not created in Apex); designed for heavy, read-dominant access.

• They help reads (reports/list views/SOQL) but don’t improve writes and don’t change governor limits.

• Schema tweaks (adding/removing fields you need) require coordination — they’re not auto-expanded.

• Not a substitute for solid indexing, selective SOQL, and a clean data model.

• Use when the object is very wide, queries are selective, and you still see latency after tuning.

Your code doesn’t change — you still write normal SOQL; Salesforce routes lookups through the skinny where it applies:

// You don't reference the skinny table directly:
List<MyObject__c> rows = [
    SELECT Id, Name, Status__c, Region__c, MRR__c
    FROM MyObject__c
    WHERE Status__c = 'Active' AND Region__c IN : new Set<String>{'EMEA','AMER'}
    ORDER BY LastModifiedDate DESC
    LIMIT 200
];

---

Decision Flow Diagram

This diagram visualizes the process of choosing the right tool and optimization technique.

---

Decision Flow Diagram

This diagram illustrates how to choose between SOQL, SOSL, and when to consider Skinny Tables based on your use case and performance profile.

---

Short summary

Use SOQL for precise, selective queries when you know the object. Use SOSL for full-text, cross-object discovery and type-ahead. Tune for relevance with selective filters, prefix wildcards, snippets, and slim payloads. Consider Skinny Tables only after proper tuning/indexing — they’re great for read-heavy, wide objects, but they add operational overhead and don’t help writes or limits.