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.

3
  • If you changed exampleDate to be a DateTime, then you wouldn't need to use the DAY_ONLY() function in the where clause. Jun 21, 2016 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, 2016 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. Jun 21, 2016 at 2:13

1 Answer 1

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

3
  • +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, 2016 at 2:13
  • Well, it does work with date literals. Which I think your answer really helps explain.
    – Adrian Larson
    Jun 21, 2016 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, 2016 at 2:33

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .