In Apex, one can query up to 50,000 records in an execution context. This applies to a regular SOQL Query where records are returned such as this:
However, it also applies to count SOQL queries, despite only one record being returned. Salesforce applies the limit because it still has to iterate over the records to determine the count.
If the count SOQL query counts more than 50,000 rows, you’ll receive an error message like this:
“System.LimitException: Too many query rows: 50001”
Workarounds
Execute SOQL Query using REST / SOAP API
The Salesforce Partner or Enterprise API allows one to execute a SOQL query against it. Since it has paging built into it using a cursor behind the scenes, it’s not subject to the 50,000 row limit. One can invoke the Query endpoint with the count SOQL query to get the true number of results.
For one-off counts, often done while troubleshooting or to determine the design approach, issue the SOQL query using Workbench or through the Developer Console. Behind the scenes, they’re using the Salesforce APIs to execute the query so they won’t run into the 50,000 row limit.
As part of a Salesforce feature, one can write Apex that invokes a Salesforce API to get the count. See my Calling Salesforce Web Services Using Apex Cookbook Recipe, for detailed instructions on how to implement that.
VisualForce ReadOnly Page
If the count SOQL query is executed from a VisualForce page and the page doesn’t do any DML, aka insert, update, or delete records, mark the page as “ReadOnly”. This allows up to 1 million records to be queried.
See Salesforce Read-Only Mode Documentation for more details.
Count the SOQL count() query as a single row query Idea
One way the Salesforce community can provide feedback is through Ideas. An idea is a request that can be upvoted or downvoted by the community and commented on. After a certain threshold, Salesforce will place the Idea under review and potentially implement it.
Please upvote the Count the SOQL count() query as a single row query Idea so that hopefully no workarounds are needed in the near future.
What other workarounds and bits of wisdom do you have? Let us know in the comments below.
Happy Coding,
Luke