You determine what constitutes the optimal set of database
indexes for your system and needs. The benefits of faster queries
must be weighed against the performance costs that database indexes
impose for object creation and object updates.
Necessary indexes
For some simple queries, the necessary indexes can be easily
identified and readily created with the administration console.
Unnecessary indexes
Slow query performance can sometimes be preferable to the
costs that are imposed by property indexes. This trade-off is especially
relevant for queries whose search conditions reference dozens of properties.
Property indexes can cause object creation to be slower and also add
to storage costs.
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.
Not every property needs to be indexed
Do not attempt to cover the entire set of SELECT or WHERE columns
with a large composite index or SQL Server "include columns". Such
a solution is expensive in terms of storage costs, ingestion overhead,
and statistics run time. Also, future changes might break the solution
when either user or system property columns are added to the SELECT list.
These changes might occur during Content Platform Engine upgrades, metadata
authoring, or changes in the search.