A

Anonymous

Solved

Create new index for a query

14 replies
3683 views
Translate All

I am trying to create a new index for a slow query. I've tried many variations of an oak:QueryIndexDefinition (tried both property and lucene types), but none seem to apply for this query. Please help me understand this.

From the ACS Explain Query Tool:

{ "statement": "SELECT [fileReference] FROM [nt:unstructured] WHERE ISSAMENODE([/content/demo-spark/jcr:content/bgimage]) OR ISSAMENODE([/content/demo-spark/fr_FR/jcr:content/bgimage])", "language": "JCR-SQL2", "explain": { "logs": [ "cost using filter Filter(query=explain SELECT [fileReference] FROM [nt:unstructured] WHERE ISSAMENODE([/content/demo-spark/jcr:content/bgimage]) OR ISSAMENODE([/content/demo-spark/fr_FR/jcr:content/bgimage]), path=*)", "cost for aggregate lucene is Infinity", "Evaluating plan with index definition Lucene Index : /oak:index/damAssetLucene", "No applicable IndexingRule found for any of the superTypes [nt:unstructured, nt:base]", "Evaluating plan with index definition Lucene Index : authorizables(/oak:index/authorizables)", "No applicable IndexingRule found for any of the superTypes [nt:unstructured, nt:base]", "Evaluating plan with index definition Lucene Index : /oak:index/lucene", "Index is old format. Not supported", "Evaluating plan with index definition Lucene Index : cq:Page(/oak:index/cqPageLucene)", "No applicable IndexingRule found for any of the superTypes [nt:unstructured, nt:base]", "Evaluating plan with index definition Lucene Index : tags(/oak:index/ntBaseLucene)", "Applicable IndexingRule found IndexRule: nt:base", "Evaluating plan with index definition Lucene Index : cqTag(/oak:index/cqTagLucene)", "No applicable IndexingRule found for any of the superTypes [nt:unstructured, nt:base]", "Evaluating plan with index definition Lucene Index : workflow(/oak:index/workflowDataLucene)", "No applicable IndexingRule found for any of the superTypes [nt:unstructured, nt:base]", "cost for lucene-property is Infinity", "cost for reference is Infinity", "getPlans(Filter, List<OrderEntry>, NodeState)", "getPlans() - filter: Filter(query=explain SELECT [fileReference] FROM [nt:unstructured] WHERE ISSAMENODE([/content/demo-spark/jcr:content/bgimage]) OR ISSAMENODE([/content/demo-spark/fr_FR/jcr:content/bgimage]), path=*) - ", "getPlans() - sortOrder: null - ", "getPlans() - rootState: { jcr:primaryType = rep:root, jcr:mixinTypes = [rep:AccessControllable, rep:RepoAccessControllable], sling:target = /index.html, :childOrder = [bin, rep:repoPolicy, rep:policy, jcr:system, var, libs, etc, home, content, favicon.ico, system, apps, conf, tmp], sling:resourceType = sling:redirect, :async : { async-temp = [9995c2a6-3940-4e31-a8a4-5f0f4e11ced0, 40f256b6-a463-41e9-bdc7-8a1d5187da61], async = 9995c2a6-3940-4e31-a8a4-5f0f4e11ced0, async-LastIndexedTo = 2015-08-20T15:19:07.578Z }, tmp = { ... }, home = { ... }, favicon.ico = { ... }, :clusterConfig : { :clusterId = 101ee51d-0352-4933-96d5-e372aed2c867 }, oak:index = { ... }, conf = { ... }, jcr:system = { ... }, etc = { ... }, system = { ... }, content = { ... }, bin : { jcr:primaryType = nt:folder, jcr:mixinTypes = [mix:versionable], jcr:createdBy = admin, jcr:versionHistory = b636ebc7-cf9a-430f-a276-10706b37f8c4, jcr:predecessors = [], jcr:created = 2014-04-02T21:40:10.723Z, jcr:baseVersion = c3735025-3e64-4d0e-b132-9dc08d0193c1, jcr:isCheckedOut = false, jcr:uuid = 40ed38c9-cb13-41dc-8602-0249e2c1b4c2 }, rep:policy = { ... }, libs = { ... }, rep:repoPolicy = { ... }, var = { ... }, apps = { ... } } - ", "cost for ordered is Infinity", "cost for nodeType is Infinity", "cost for property is Infinity", "cost for traverse is 1303100.0" ], "plan": "[nt:unstructured] as [nt:unstructured] /* traverse \"*\" */", "traversal": true, "slow": true } }

/content/demo-spark/oak:index/fileReferenceLucene.-1.json:
(I've also tried creating this as a root index; /oak:index/fileReferenceLucene)

{ "jcr:primaryType": "oak:QueryIndexDefinition", "compatVersion": 2, "name": "nt:base/fileReference", "type": "lucene", "async": "async", "reindex": false, "reindexCount": 7, "indexRules": { "jcr:primaryType": "nt:unstructured", "nt:base": { "jcr:primaryType": "nt:unstructured", "includePropertyTypes": "all", "properties": { "jcr:primaryType": "nt:unstructured", "fileReference": { "jcr:primaryType": "nt:unstructured", "propertyIndex": true, "name": "fileReference" } } } } }

Is there something that I'm missing? Why can I not get this index to apply against the slow query?

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

Sham_HCAccepted solution

Level 10

Hmmm.. Sorry missed the description. From Explain query output your index not even listed that means your custom nt:base rule would over shadow the default nt:base rule. Looking into your query & based on path "/content/demo-spark/jcr:content/bgimage" I am assuming "demo-spark" is cq:Page. So modify your index defnition to node type CQ:Page OR made if you modify the OOB one to include your defnition.

A

Anonymous

Thanks, bsloki. In what way are you verifying that the index has been used?

Translate

Sham_HC

Level 10

enable debug log Or use http://adobe-consulting-services.github.io/acs-aem-tools/explain-query.html

Translate

smacdonald2008

Level 10

YOu are welcome - we know this is an area where community members like you need more working examples and content.

Translate

A

Anonymous

*bump*

We're still looking for help. Can anybody explain why the created index wasn't working?

I've also tried creating a 'property' index:

{ "jcr:primaryType": "oak:QueryIndexDefinition", "propertyNames": ["fileReference"], "type": "property", "reindex": false, "reindexCount": 1 }

Translate

A

Anonymous

Thank you.

Translate

Lokesh_Shivalingaiah

Level 10

It should pick as it does for me on my test. Can you file a day care support ticket so that they can look at your code and analyse the same

Translate

smacdonald2008

Level 10

I will pass this to our OAK experts. Like i mentioned in the previous comment- our community members are going to give this area more attention and have our own working examples. You will soon see working examples come out. For now - i have passed this to some of our experts.

Translate

A

Anonymous

Thanks Scott, but following that document is what got me into the current situation.

If you compare my index definition (above) to the Oak documentation, the definition appears to be compliant. It does not, however, seem to be working. This is why I have posted, I'm trying to understand why my index definition is not being used for the query (above).

Translate

smacdonald2008

Level 10

Here is good OAK docs on this subject:

http://jackrabbit.apache.org/oak/docs/query/lucene.html

Moving forward - we are going to give this area some attention in the community. We will be doing some articles and base an AEM Ask the Community Experts on this topic.

Translate

A

Anonymous

Yes... the query is as above.

SELECT [fileReference] FROM [nt:unstructured] WHERE ISSAMENODE([/content/demo-spark/jcr:content/bgimage]) OR ISSAMENODE([/content/demo-spark/fr_FR/jcr:content/bgimage])

Translate

Show more replies

Sign up

Login with SSO

Login to the community

Login with SSO