Unnecessary indexes for system properties

Sometimes, system properties are added by the Content Platform Engine to the database SQL query. Most system columns that require indexes are already indexed automatically when an object store is created.

The following example of database SQL shows some of the system columns that might be added to a query:

WHERE T0.object_class_id 
IN (?, ?) 
AND T0.home_id Is Null 
AND (T0.is_current = ? OR ( T0.version_status = ? AND T0.is_reserved = ?))

For reasons that are discussed in the following table, you typically do not need to create database indexes for system properties.

Table 1. System properties that do not require database indexes
System Column	Comments
`home_id`	A column such as `home_id` is used for cross-object store references. It is always null or perhaps has one value (for example, in an IBM® Enterprise Records system). Indexing this column does not typically help search performance except in some IBM Enterprise Records cases. An index on this column is not used in a query execution plan if all rows are null because no row filtering can occur. Also, adding `home_id` to a composite index does not help performance either in cases where no row filtering can occur. Add a column to a composite index only if the search condition significantly reduces the number of rows that are filtered. For more information, see Index tuning: Selective search conditions. The presence of `home_id` in the search conditions also does not incur any noticeable expense in the performance of a search. Because the value is rarely not null, there is always a hit on the row that is returned from the other search conditions. Although the row that is returned from the main data page has to be checked for the `home_id is null` condition, this filter step is fast. In all cases, reports that a query can be made faster by removing the `home_id is null` clause were the result of faulty analysis.
`is_current`, `version_status`, `is_reserved`	These columns on the DocVersion table have low cardinality (the number of unique values is small), and as such are not good candidates for indexing: the value that is searched for is typically the same value that most rows have. When the values are mostly the same, an index is likely not used because scanning the table is faster than the alternative: scanning the index and switching back and forth to the main table to access all of the rows.
`object_class_id`	This column stores the ID of the class for the object. It is also of typically low cardinality. The same arguments apply here as with the other system columns, but a distinction can be made in the case of searching for a specific class. For more information, see Index tuning: No search conditions (continuable query).