Picking wrong index because of its cost [ AEM Index ]

[manideep28]

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.

[Saravanan_Dharmaraj · Accepted solution]

Have you tried this ? 

• If your queries are only being run under certain paths, then create those indexes under those paths. Indexes are not required to live at the root of the repository.

Please try to reindex it and see?

https://experienceleague.adobe.com/docs/experience-manager-64/deploying/practices/best-practices-for... 

[manideep28]

Thanks alot @Saravanan_Dharmaraj , It worked after keeping index definition node inside the folder like

/content/dam/xxx/content-fragments/vegetables/oak:index/searchVegetables.