Sometimes search conditions require too much database row filtering. You can prevent excessive row filtering with a composite index.
SELECT Id
FROM Document
WHERE Property1 = 'value1'
AND Property2 = 'value2'
ORDER BY Property2
Assume that an index is already present on Property1, and that the cardinality of Property1 is greater than Property2. (Cardinality is the number of unique values for a property.) As the index on Property1 is traversed, many rows might have to be filtered.
Query type | Example |
---|---|
A query that returns relatively few results |
|
A query that returns many results |
|
Use the administration console to drop the database index on Property1, if present.
This solution is for a relatively simple query. More complex queries usually have several other search conditions that apply to several different columns. Some of these search conditions might be only slightly selective; that is, they might filter relatively few rows. Adding only the more selective columns to a database index is typically best for the following reason: adding more columns to a composite index is costly in terms of both storage and object creation costs.