[SalesForce] Difference between IN and = operator in SOQL query

From the documentation on Large Data Volumes best practices: (Page 10)

In some cases, when multiple WHERE filters are being used in SOQL, indexes cannot be used even though the fields in the WHERE clause can be indexed. In this situation, decompose the single query into multiple queries, each of which should have one WHERE filter, and then combine the results.

and

Executing a query with a WHERE filter that has null values for picklists or foreign key fields doesn’t use the index, and should be avoided.

So, while you've got two selective queries - the combination of the two isn't selective and the optimizer isn't able to use indexes. Most likely because of the usage of the foreign key field (Lookup: mdr_Parent__c) in the where clause because the results of the query in the where clause could (theoretically) include nulls and a table scan will be performed to find the matching rows as no index can be used in that situation.

Queries which reference a lookup field will result in a table scan even if your where clause specifies qualifiers like WHERE mylookupfield__c != null.

Small Backstory

I've had to troubleshoot non-selective queries recently where what I thought was written was a good/best practice, specifically checking for != null or = null on lookup field values (as well as other types) in an effort to make the query more efficient. I was under the assumption that this would improve performance by excluding or including rows which had null values in the lookup fields by making use of an index. Unfortunately, this is not the case. These queries perform a table scan and it's extremely inefficient, especially noticeable with large volumes.

The most impressive optimization I've seen recently was a query (in a batch) which was taking 13 minutes to return ~400k records. The platform would shut down the batch when it reached the max processing time and the solution eventually needed to scale to a data volume of 2M+ records. Optimizations were in order.

After reading that LDV best practices document and then changing the structure of the query being performed the difference was quite staggering. The rewritten query returned 1.6M records in under 3 minutes and now scales incredibly well.

Beware of table scans because of referencing foreign keys in where clauses.

The first is casting. The second is calling a static method on the Id class.

One major advantage of the former is that it is more null safe.

Id idVal1 = (Id)null; // works
Id idVal2 = Id.valueOf(null); // blows up

One advantage of the latter is that you can one-line more of your code by calling instance methods on the cast result. You can get one-liners by adding some parentheses to the casting approach, though some might object on stylistic grounds. I find it a little more visually confusing.

SObjectType t1a = (Id)'001000000000000AAA'.getSObjectType(); // blows up
SObjectType t1b = ((Id)'001000000000000AAA').getSObjectType(); // works
SObjectType t2 = Id.valueOf('001000000000000AAA').getSObjectType(); // works

The above pros/cons are independent of type, so you can carry these conclusions over to String, Decimal, etc.