How to Analyze Queries and create OAK Index definitions in AEM(Adobe Experience Manager)

In AEM there are many tools to diagnosis the AEM repository. Query performance manager is one of them. By using this tool, we can find both slow and popular queries. And, it will explain the given query which index it is being used, how much time will take to run. This will explain the JCR-SQL2, SQL, XPath, and Query Builder queries. 

To open the Query performance Manager tool. From AEM start page Navigate to Tools --> Operations --> Diagnosis --> Query Performance 

Navigate to Query performance tool. Where you can see the Slow queries and Popular Queries. you can select any query and click on the explain query to see which index definition used and how much time it took to execute. 

When do we need to create a Query index definition?

When your query executes, you will see the warning logger messages like below 

*WARN* [sling-default-4-com.sony.sie.scheduler.ActivatePageSchedulerUS:05e1d8c7-d160-4ac5-8133-7d63c940ac1b] org.apache.jackrabbit.oak.query.QueryImpl Traversal query (query without index): <your query> consider creating an index 

Or you in Query performance tool you will find the slow queries. To optimize those, you can create Oak Index Definition to index content for your queries. 

How to create Oak Index Definition for your Query 

In AEM there few OOTB index definitions that are created under “/oak:index”. You can create your own index definition under this path. You create the two types of indexes property index or Lucence Index. Oak Index Definition Generator will help you to create Oak index definitions based on your queries. You can input your query it will generate the Index definition format as Text/ JSON / XML. For AEM choose XML format. After generating the XML compare it with any OOTB definition and make sure the property types are matching or not? You may see the below warning messages if the property definition is not configured properly. For property, definitions refer to the documentation. 

“org.apache.jackrabbit.oak.plugins.index.property.PropertyIndexLookup Expected 'NAMES' as type of

property 'propertyNames' but found 'STRING'. Node - '{ jcr:primaryType = oak:QueryIndexDefinition, 

jcr:createdBy = Ensure Oak Index, declaringNodeTypes = [cq:AuditEvent], propertyNames = cq:time,

jcr:created = 2020-06-03T17:52:47.983Z, type = property, reindex = false, reindexCount = 3, 

:index = { ... } }' 

Refer documentation when to use the Lucence index and Property index.