My query is not picking up custom index | Community
Skip to main content
N
navneet_77
New Member
November 27, 2023
Solved

My query is not picking up custom index

  • November 27, 2023
  • 3 replies
  • 1761 views

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!

 

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by aanchal-sikka
  • 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.

3 replies

Saravanan_Dharmaraj
Community Advisor
Saravanan_DharmarajCommunity Advisor
Community Advisor
November 27, 2023

@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-queries-and-indexing.html?lang=en#should-i-create-an-index 

 

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

 

    N
    navneet_77Author
    New Member
    December 5, 2023

    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
      Community Manager
      kautuk_sahniCommunity Manager
      Community Manager
      December 4, 2023

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

      Kautuk Sahni
      aanchal-sikka
      Community Advisor
      aanchal-sikkaCommunity AdvisorAccepted solution
      Community Advisor
      July 9, 2024
      • 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.

      Aanchal Sikka
        F
        FilippoDT
        Level 2
        October 28, 2024

        Hi,

         

        is it possible to have non root indexes on AEMaaCS too?

        On the official website, e.g. [1], I only found examples regarding indexes put under /oak:index and warnings telling not to use more than one index per node type.

         

        Filippo

         

        [1] https://experienceleague.adobe.com/en/docs/experience-manager-cloud-service/content/operations/indexing

        Terms & ConditionsAccessibility statement

        Loading footer...