Index on History Table Fields

Question

We want to query the Case History table to count how many times the Case Status is changed from Status A to B and B to A.

We have around 10 million Case History records in production. Want to know if there is any index applied to History Table. If not, is it possible to create Custom Index? Can't find this information in documentation.

Any help is appreciated. Thanks.

Answer 1

Take your query and run it through the Query Plan tool. The tool will tell you if theres an index involved, and show you the costs of each option to run the query. When run, the query will execute the cheapest option available. By using this tool on slow queries, you might be able to target different fields & increase their speed.

Running this query through the tool shows:

SELECT Id, AccountId FROM AccountHistory WHERE AccountId = '001c000001OIXIsAIA'

As you can see, it specifies if an index is involved, and provides cost values for each query plan. For this query, it'll check based on the indexed field. For queries without an indexed field in the filter, it would have to run a table scan.

Answer 2

By default, the following fields are indexed

• Primary keys (Id, Name and Owner fields).
• Foreign keys (lookup or master-detail relationship fields)
• Audit dates (such as SystemModStamp)
• Custom fields marked as External ID or Unique

The history object should also have a primary key and will be indexed. Do raise a case with Salesforce support to re-confirm.