Picking wrong index because of its cost [ AEM Index ]
Hi all,
We have a requirement to improve the performance for fetching content-fragments in search-query & preferred to create indexes for different scenario based queries used across various searches in site.
Example Scenario : ( taking few general items & explained below )
There are few types of content-fragment models like fruits & vegetables and creating content-fragments using those models respectively under below folders.
1. /content/dam/xxx/content-fragments/fruits
2. /content/dam/xxx/content-fragments/vegetables
Created separate lucene-index for dam:Asset as below & both indexes are working separately ( when only one index is present under oak:index & indexed in repository ) but, if we are keeping both indexes as is then we are facing an issue.
For fruits : (sorted by lastmodified)
Query : select [jcr:path], [jcr:score], * from [dam:Asset] as a where [jcr:content/data/cq:model] = '/conf/xxx/settings/dam/cfm/models/fruits' and isdescendantnode(a, '/content/dam/xxx/content-fragments/fruits') order by [jcr:content/jcr:lastModified] desc
Index :
<searchFruits compatVersion="{Long}2" async="async" jcr:primaryType="oak:QueryIndexDefinition" evaluatePathRestrictions="{Boolean}true" type="lucene">
<indexRules jcr:primaryType="nt:unstructured">
<dam:Asset jcr:primaryType="nt:unstructured">
<properties jcr:primaryType="nt:unstructured">
<model name="jcr:content/data/cq:model" propertyIndex="{Boolean}true" jcr:primaryType="nt:unstructured"/>
<lastModified name="jcr:content/jcr:lastModified" ordered="{Boolean}true" jcr:primaryType="nt:unstructured"/>
</properties>
</dam:Asset>
</indexRules>
</searchFruits>
For vegetables : (sorted by title)
Query : select [jcr:path], [jcr:score], * from [dam:Asset] as a where [jcr:content/data/cq:model] = '/conf/xxx/settings/dam/cfm/models/vegetables' and isdescendantnode(a, '/content/dam/xxx/content-fragments/vegetables') order by [jcr:content/jcr:title]
Index :
<searchVegetables compatVersion="{Long}2" async="async" jcr:primaryType="oak:QueryIndexDefinition" evaluatePathRestrictions="{Boolean}true" type="lucene">
<indexRules jcr:primaryType="nt:unstructured">
<dam:Asset jcr:primaryType="nt:unstructured">
<properties jcr:primaryType="nt:unstructured">
<model name="jcr:content/data/cq:model" propertyIndex="{Boolean}true" jcr:primaryType="nt:unstructured"/>
<title name="jcr:content/jcr:title" ordered="{Boolean}true" jcr:primaryType="nt:unstructured"/>
</properties>
</dam:Asset>
</indexRules>
</searchVegetables>
Issue :
The cost of vegetable index is lesser than the fruits as shown in "explain query" tool - so everytime when we are diagnosing the query of fruits in "explain query" tool, it is picking vegetables ratherthan fruits & ending up with zero results, because we have used includedPaths & excludedPaths properties.
Need help in resolving it. Let me know if any additional info needed. Thanks inadvance.