Consider Creating an Index in SQL2 Query | Community
Skip to main content
A
aemUser2345
Level 3
February 1, 2024
Solved

Consider Creating an Index in SQL2 Query

  • February 1, 2024
  • 2 replies
  • 1382 views

I have a SQL2 query that needs indexing: SELECT * FROM [nt:base] AS s WHERE ISDESCENDANTNODE([/content/homepage/jcr:content])

 

But I got this error:

no proper index was found for filter Filter(query=explain SELECT * FROM [nt:base] AS s WHERE ISDESCENDANTNODE([/content/homepage/jcr:content]) , path=/content/homepage/jcr:content//*) Traversal query (query without index): explain SELECT * FROM [nt:base] AS s WHERE ISDESCENDANTNODE([/content/homepage/jcr:content]) ; consider creating an index

 

I was wondering which field I could create index for here? Because I don't see any fields being used that I can index...

 

Thanks~!

Best answer by PAGIDALAGURUKRISHNA

Based on your query, which is selecting all nodes within a certain sub-tree in the repository, you will want to create an index that optimizes for path queries. Since your query does not filter based on properties but rather on the node’s position in the repository hierarchy, a path index is the most suitable. Here is a breakdown of the recommended steps to create a path index based on the AEM documentation:

  1. Access CRX DE: Open the CRX DE Lite tool to navigate the repository and create your index definition.
  2. Create the Index Definition Node: Navigate to the /oak:index node and create a new node for your custom index definition. You might name it something descriptive, such as homepageDescendants.
  3. Configure the Index: Set the properties for your index node:
    • Set the jcr:primaryType to oak:QueryIndexDefinition.
    • Set the type property to path.
    • Set reindex to true if this is a new index or if you want to reindex.
    • Add an includedPaths property and set it to [/content/homepage/jcr:content] to include only the descendants of this path.
  4. Reindex: After saving the changes, the new index will need to be reindexed. This is done automatically if reindex is set to true.
  5. Test the Query: Once reindexing is complete, test your query again to ensure that it is now using the new index.

Here’s an example of what the node structure for your index definition might look like in CRX DE:

<homepageDescendants
    jcr:primaryType="oak:QueryIndexDefinition"
    type="path"
    reindex="true">
    <indexRules jcr:primaryType="nt:unstructured">
        <nt:base jcr:primaryType="nt:unstructured">
            <properties jcr:primaryType="nt:unstructured">
                <path jcr:primaryType="nt:unstructured"
                       name="jcr:path"
                       unique="false"/>
            </properties>
        </nt:base>
    </indexRules>
    <includedPaths jcr:primaryType="nt:unstructured">
        <content jcr:primaryType="nt:unstructured" name="/content/homepage/jcr:content"/>
    </includedPaths>
</homepageDescendants>

This structure tells AEM to index the paths under `/content/homepage/jcr:content`, which should optimize your query. For further reading and more detailed instructions on how to create and manage custom indexes, you can refer to the following documentation:

2 replies

Hemant_arora
Hemant_arora
Level 8
February 1, 2024

GO to this url https://oakutils.appspot.com/generate/index

 

paste query and generate index definition, copy xml format and add to oak:index/.content.xml and update filter.xml to deploy

    T
    Adobe Employee
    ThomasMueller1Adobe Employee
    Adobe Employee
    September 4, 2025

    This tool is replaced with https://thomasmueller.github.io/oakTools/indexDefGenerator.html

    PAGIDALAGURUKRISHNA
    Adobe Employee
    PAGIDALAGURUKRISHNAAdobe EmployeeAccepted solution
    Adobe Employee
    February 1, 2024

    Based on your query, which is selecting all nodes within a certain sub-tree in the repository, you will want to create an index that optimizes for path queries. Since your query does not filter based on properties but rather on the node’s position in the repository hierarchy, a path index is the most suitable. Here is a breakdown of the recommended steps to create a path index based on the AEM documentation:

    1. Access CRX DE: Open the CRX DE Lite tool to navigate the repository and create your index definition.
    2. Create the Index Definition Node: Navigate to the /oak:index node and create a new node for your custom index definition. You might name it something descriptive, such as homepageDescendants.
    3. Configure the Index: Set the properties for your index node:
      • Set the jcr:primaryType to oak:QueryIndexDefinition.
      • Set the type property to path.
      • Set reindex to true if this is a new index or if you want to reindex.
      • Add an includedPaths property and set it to [/content/homepage/jcr:content] to include only the descendants of this path.
    4. Reindex: After saving the changes, the new index will need to be reindexed. This is done automatically if reindex is set to true.
    5. Test the Query: Once reindexing is complete, test your query again to ensure that it is now using the new index.

    Here’s an example of what the node structure for your index definition might look like in CRX DE:

    <homepageDescendants
    jcr:primaryType="oak:QueryIndexDefinition"
    type="path"
    reindex="true">
    <indexRules jcr:primaryType="nt:unstructured">
        <nt:base jcr:primaryType="nt:unstructured">
            <properties jcr:primaryType="nt:unstructured">
                <path jcr:primaryType="nt:unstructured"
                       name="jcr:path"
                       unique="false"/>
            </properties>
        </nt:base>
    </indexRules>
    <includedPaths jcr:primaryType="nt:unstructured">
        <content jcr:primaryType="nt:unstructured" name="/content/homepage/jcr:content"/>
    </includedPaths>
</homepageDescendants>

    This structure tells AEM to index the paths under `/content/homepage/jcr:content`, which should optimize your query. For further reading and more detailed instructions on how to create and manage custom indexes, you can refer to the following documentation:

      A
      aemUser2345Author
      Level 3
      February 1, 2024

      Thanks for your reply! This is super thorough. I was wondering if I have thousands of pages, do I need to add these paths one by one like below? 

       

      <includedPaths jcr:primaryType="nt:unstructured">
        <content jcr:primaryType="nt:unstructured" name="/content/homepage0/jcr:content"/>
              <content jcr:primaryType="nt:unstructured" name="/content/homepage1/jcr:content"/>
              <content jcr:primaryType="nt:unstructured" name="/content/homepage2/jcr:content"/>
</includedPaths>

       I was wondering if there's any better way to do this? 

        Terms & ConditionsAccessibility statement

        Loading footer...