Consider Creating an Index in SQL2 Query

[aemUser2345]

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~!

[Hemant_arora]

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

[ThomasMueller1]

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

[PAGIDALAGURUKRISHNA · Accepted solution]

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:

• Best Practices for Queries and Indexing
• Oak Queries and Indexing
• Content Search and Indexing in AEM as a Cloud Service

[aemUser2345]

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? 

[ThomasMueller1]

There is a new tool to help generate index definitions: https://thomasmueller.github.io/oakTools/indexDefGenerator.html