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.