My query is not picking up custom index

[navneet_77]

I want to get the asset nodes for the given expiry dates for ACS commons report generation. The OOTB index "/oak:index/damAssetLucene" is picked by default by i am getting an error that query need to traverse more than 180000 nodes. So i created a custom index but still AEM is picking up the OOTB index only. 

Pls let me know where i am making mistake. 

Sample Query:

SELECT * FROM [dam:Asset] AS NODE WHERE ISDESCENDANTNODE ('/content/dam') AND NODE.[/jcr:content/metadata/prism:expirationDate] >= cast('2023-03-15T00:00:00.000+05:30' as date) AND NODE.[/jcr:content/metadata/prism:expirationDate] <= cast('2023-06-15T00:00:00.000+05:30' as date)

Index Node Created:

<cqAssetExpiry

        jcr:primaryType="oak:QueryIndexDefinition"

        async="[async]"

        compatVersion="{Long}2"

        evaluatePathRestrictions="{Boolean}true"

        includedPaths="[/content/dam]"

        queryPaths="[/content/dam]"

        reindex="{Boolean}false"        

        seed="{Long}5529323364102785708"

        type="lucene">

        <indexRules jcr:primaryType="nt:unstructured">

            <dam:Asset jcr:primaryType="nt:unstructured">

                <properties jcr:primaryType="nt:unstructured">

                    <expirationDate jcr:primaryType="nt:unstructured"

                        name="jcr:content/metadata/prism:expirationDate" ordered="true"

                        propertyIndex="true" type="Date" />

                </properties>

            </dam:Asset>

        </indexRules>

    </cqAssetExpiry>

Thanks in advance!

[Saravanan_Dharmaraj]

@navneet_77 Could you move the index definition under /content/dam and reindex it and test it out and see if the AEM is picking it?

• 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.

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

Try the query with explain query tool and see the response on why its picking and the cost associated.

https://adobe-consulting-services.github.io/acs-aem-tools/features/explain-query/index.html

[navneet_77]

Hi Saravan,

Thanks for your suggestion, but still i am facing same issue. Please let me know if there are any other directions/sugeestions which i can try out.

Thanks.

[kautuk_sahni]

@navneet_77 Did you find the suggestions from users helpful? Please let us know if more information is required. Otherwise, please mark the answer as correct for posterity. If you have found out solution yourself, please share it with the community.

[aanchal-sikka · Accepted solution]

• Avoid overlapping index definition - Do not have overlapping index definition indexing same nodetype but having different includedPaths and excludedPaths. Index selection logic does not make use of the includedPaths and excludedPaths for index selection. Index selection is done only on cost basis and queryPaths. Having multiple definition for same type would cause ambiguity in index selection and may lead to unexpected results. Instead, have a single index definition for same type.

• Non root indexes - If your query always perform search under certain paths then create index definition under those paths only. This might be helpful in multi tenant deployment where each tenant data is stored under specific repository path and all queries are made under those path.
  
  In fact, it's recommended to use single index if all the properties being indexed are related. This would enable Lucene index to evaluate as much property restriction as possible natively (which is faster) and also save on storage cost incurred in storing the node path.