Force.com Query Optimizer (Lightning Platform Query Optimizer)

 Force.com Query Optimizer is the primary command center in Force.com Platform that is responsible for executing the SOQL/Filter statements and returning the data back to the code/Front-end.

The Force.com query optimizer tool optimizes the SOQL used to fetch data by inspecting SOQL,Reports and List Views.

The Force.com query optimizer is an engine that sits between your SOQL ,reports and list views and the database itself.

Because of salesforce.com's multitenancy, the optimizer gathers its own statistics instead of relying on the underlying database statistics.

Pre-computed Statistics contains the following information's.

1.Row Count

2.User Visibility

3.Custom index

4.Owner row Count

First, Force.com executes “pre-queries” that consider the multitenant-aware statistics. Then, using the results returned by the pre-queries, the service builds an optimal underlying database query for execution in the specific setting. Using both these statistics and pre-queries, the optimizer generates the most optimized SQL to fetch your data. It looks at each filter in your WHERE clause to determine which index, if any, should drive your query.

To determine if an index should be used to drive a query, the Force.com query optimizer checks the number of records targeted by the filter against selectivity thresholds.

Lightning Platform Query Optimizer :

When a developer invokes SOQL, it’s the responsibility of a technology called the query optimizer to translate it into SQL. The query optimizer dynamically adapts its query to select and join tables in the most efficient way possible depending on the filters, fields, and other properties involved in the query. It also enforces sharing and record access rules to guarantee data privacy. Finally, it ensures the underlying SQL query only retrieves records that belong to the customer environment that issued the SOQL request.

To ensure that your queries are selective, avoid some common pitfalls.

1.Understand your schema and have proper indexes created.

2.Apply as many filters as possible to reduce the result set.

3.Minimize the amount of records in the Recycle Bin.

4.Remember that NOT operations and LIKE conditions with a leading % wildcard do not use indexes, and complex joins might perform better as separate queries.