+ Start a Discussion
LVSLVS 

Working with very large SOQL - Is LIMIT statement considered for selectivity threshold?

According to http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_VLSQ.htm :

Selective SOQL Query Criteria

  • A query is selective when one of the query filters is on an indexed field and the query filter reduces the resulting number of rows below a system-defined threshold. The performance of the SOQL query improves when two or more filters used in the WHERE clause meet the mentioned conditions.
  • The selectivity threshold is 10% of the records for the first million records and less than 5% of the records after the first million records, up to a maximum of 333,000 records. In some circumstances, for example with a query filter that is an indexed standard field, the threshold may be higher. Also, the selectivity threshold is subject to change.

 

In this, does the 'number of records' returned from the query respect the LIMIT statement? What I mean is, if I have a filter condition that matches 2,00,000 records but I have a LIMIT 50000 on the statement, is the number of records considered as 50,000 or 2,00,000?

 

Why question is born from the fact that the above statements mention query filter reducing the results and not LIMIT statements explicitly

gaisergaiser

I am guessing that Limit part makes no difference to selectivity threshold.

 

Consider what query engine has to do in the order it has to do it.

First 'where', 'group by', 'order by', etc is applied and then goes Limit part which has to be the very last step on top of all previous steps, otherwise returned result will be incorrect.

 

So by the time it gets to Limit phase selectivity threshold may have already been reached and governor limit exception thrown.

But as I said above - this is just a guess and I have not seen this part documented.

LVSLVS
that's what I arrived at too. But documented is always better