Below are the difference of Selective and Non-Selective SQL Query.
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. And the performance of the SQL query improves when two or more filters used in the WHERE clause meet the mentioned conditions.
To better understand whether a query on a large records is Selective or not, let’s analyze some queries. For these queries, assume that there are more than 500,000 records for the Account Table. These records include soft-deleted records, that is, deleted records that are still in the Recycle Bin.
Query 1: (For SELECTIVE)
SELECT Id FROM Account WHERE Id IN (<list of account IDs>)
The WHERE clause is on an indexed field (Id). If SELECT COUNT() FROM Account WHERE Id IN (<list of account IDs>) returns fewer records then, the index on Id is used. This index is typically used when the list of IDs contains only a few records. We can say this Query is SELECTIVE.
Because we got selected data on conditionally, it may produce fews records.
Query 2: (For NON – SELECTIVE)
SELECT Id FROM Account WHERE Name != ”
Since Account table having large data even though Name is indexed (primary key), this filter returns most of the records, because it’s a primariy key. So that’s why no single record will be blank/empty, so it will return all records and making the query NON-SELECTIVE.
Query 3:
SELECT Id FROM Account WHERE Name != ” AND created_date = ’08/03/2021′
Here we have to see if any filter, when considered individually, is selective. As we saw in the previous (Query 2) example, the first filter isn’t selective. So let’s focus on the second one. If the count of records returned by SELECT COUNT() FROM Account WHERE created_date = ’08/03/2021’is lower than the selectivity query, and created_date is indexed, the query is SELECTIVE.
Non-Selective queries are SQL queries (typically against) tables with more than 100k rows that bring back 100k rows. i.e. you have not specified speficially what you are looking for so a full table scan is happening and if it were to proceed too long would cause locking.
Sample error: System.QueryException: Non-Selective query against large object type (more than 100000 rows).
