For a continuable query that performs a simple property search, a composite index might improve query performance.
Content Platform Engine SQL | Corresponding database SQL (continuable query) |
---|---|
|
|
CREATE INDEX I_Property1_Id
ON DocVersion (uxy_Property1, object_id)
Use the administration console to drop the database index on Property1, if present.
By scanning the composite index, the query can directly return the rows that satisfy Property1. No sort step is required in the database query execution plan because the rows in the index are also in object_id order. The object_id order is necessary because Content Platform Engine must add the "ORDER BY object_id" clause to the database SQL to make the query continuable.
The composite index is necessary for the following reason: when there are many rows that satisfy the Property1 = 'value' condition, the "ORDER BY object_id" clause adds a significant sort cost to the query. Each returned page of rows must be sorted by object_id. Also, scanning the property1 index requires a slow row ID look-up for each row found. And so a database index on Property1 alone might not result in the inclusion of the index in the query execution plan. Instead, the query issues a table scan and then sorts the table on object_id or scans the primary key on the table.
If the query does not return many rows, the original database index on Property1 is sufficient because the sort cost is small. In this case, an alternative composite index is not best because composite indexes reduce the performance of object creation and add to storage costs.