Why does query optimization affect me as an admin? I don't write code.
If I were a betting man, I would bet that under the hood, SOQL queries, reports, list views and related lists on detail pages all tap into the same query execution framework. So, if you manage page layouts, reports and/or list views, you should care about the Force.com Query Optimizer.
What is a selective query?
A selective query is a query that leverages indexes in filters to avoid full table scans and to reduce the number of records in your result set below the selectivity threshold.
Stupid question, but what is an index?
This is a great question. Simply put, an index is a field-based mechanism by which a query can execute significantly faster, compared to execution without the index. Salesforce technical architects don't need to know how an index actually works behind the scenes, much like office workers don't need to know how a Keurig machine makes coffee at the press of a button.
A technical architect probably just needs to know that fields are either indexed or not indexed, and that indexed fields should be used in query filters. The technical architect should probably also know off-hand what standard indexes are available, and that custom indexes have lower selectivity thresholds and can only be created by Salesforce Support.
Anyone hardcore enough to dig into the Oracle database-level index machinery may want to check out the Database Systems course, offered gratis through MIT OpenCourseWare.
What's considered a standard index?
Great question! I wasn't able to find concrete documentation on this question, so you tell me (on Twitter)! I think an official answer would be a welcome addition to the "Force.com Query Optimizer FAQ" article. In the meantime, the list of indexed standard fields can be considered to all be standard indexes.
What is the selectivity threshold? And why should I care?
The selectivity threshold is the maximum number of records that can be returned in a result set, without disqualifying the index-based optimization option for a query. See the Query & Search Optimization Cheat Sheet for the exact calculations used with standard indexes and custom indexes.
Is there a difference between using nested queries vs. separate queries?
This was the first question asked on the Inside the Force.com Query Optimizer webinar during Q&A, "Is using nested queries good practice?" But I don't think the answer fully addressed the question. My personal guess (which needs to be validated in an org that actually contains large data volumes) is that nested queries have negligible impact on the execution time of a query, as long as the queries are not constructed in a way that uses the NOT operator.
The basis for my conjecture is a best guess that nested queries are executed sequentially, following an order of operations that allows the result set from one query to be used in another query. How true is this? I suppose I'll need to test all of the following query structures with selective filters applied:
More importantly, however, it's worth noting that in some instances executing separate queries in Apex is unfeasible, and the only viable alternative is to use nested queries. I could be wrong (and please correct me if I am), but one such situation could be an Apex method that needs to iterate through a result set using a for loop, processing child records for each record returned.
