2

CreateDate is a system index field maintained by Salesforce. I'd like to know when you running a query like this:

 Select Id from Account Where Day_Only(CreatedDate) > exampleDate

Does the Day_Only function make the query un-selective? Is the System index used when using soql functions? Salesforce query plan says CreatedDate is indexed, but i'm not sure it is used when running the code.

I believe Salesforce does not use the index when you use Day_Only function with a custom index.

  • If you changed exampleDate to be a DateTime, then you wouldn't need to use the DAY_ONLY() function in the where clause. – Daniel Ballinger Jun 21 '16 at 2:06
  • Sure, this will solve the problem. What I really want to know is if the system index still be used with soql functions. – Shang G Jun 21 '16 at 2:11
  • No, as per the answers, it won't in this case. The Query Plan tool is showing a full table scan. That basically means looking at ever record in the table. – Daniel Ballinger Jun 21 '16 at 2:13
2

Analysis

Using Developer Console, you can gather the following evidence for yourself when you have this type of question. Long story short, fields will not be optimized via index if they are wrapped in an expression.


The plot thickens. You actually can get an index while using an expression if you use Date Literals. The following queries do use an index:

SELECT Id FROM User WHERE DAY_ONLY(CreatedDate) >= LAST_N_DAYS:42
SELECT Id FROM User WHERE DAY_ONLY(CreatedDate) = THIS_YEAR

Evidence

Query:

SELECT Id FROM User WHERE CreatedDate > 2016-01-01T00:00:00Z

Leading Operation Type:

Index

Notes:

None

Datetime Query Plan


Query:

SELECT Id FROM User WHERE DAY_ONLY(CreatedDate) > 2016-01-01

Leading Operation Type:

TableScan

Notes:

Not considering filter for optimization because isExpression. Table: User Fields: ["CreatedDate"]

DAY_ONLY Query Plan

  • +1 I thought for sure CALENDAR_YEAR would have at least been optimized, because it would be a contiguous range in the index... but it's not. I've learned something new today. – sfdcfox Jun 21 '16 at 2:13
  • Well, it does work with date literals. Which I think your answer really helps explain. – Adrian Larson Jun 21 '16 at 2:15
  • Thanks. The class is using Day_Only(CreatedDate) > exampleDate, I use DAY_ONLY(CreatedDate) > LAST_N_DAYS:7 in the query plan. This is where my confusion from. – Shang G Jun 21 '16 at 2:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.