Create new index for a query | Community
Skip to main content
A
Anonymous
October 16, 2015
Solved

Create new index for a query

  • October 16, 2015
  • 14 replies
  • 3584 views

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.
Best answer by Sham_HC

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. 

14 replies

A
Anonymous
October 16, 2015
Sham, please re-read the question.  We have used the Explain Query Tool, its output is included in the question. Debug logging is turned on, but we're not seeing anything useful.
Sham_HC
Sham_HCAccepted solution
New Participant
October 16, 2015

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
October 16, 2015

Sham, thanks, but I don't quite follow.

You are correct in assuming that demo-spark is a cq:Page.  Because of this, shouldn't our fileReference property already be indexed as part of the cqPageLucene index (due to its aggregates)?

You mentioned "From Explain query output your index not even listed that means your custom nt:base rule would overshadow the default nt:base rule".  That does not make sense to me.  If my new index is not listed, I assume that it has not been detected (for this query).  If this is the case, how would it overshadow a different index?  Should all the indexes be maintained, and only the best one used at query time?

Forgetting about the Lucene index for a moment, I also mentioned that I tried creating a simple property-type index.  This type of index did not specify the types that it applied to.  I suspect this would not cause the overshadowing that you mentioned, but again, we did not see the custom index being used.

Lokesh_Shivalingaiah
Lokesh_Shivalingaiah
New Participant
October 16, 2015

is this your full query ?? if not can you share your actual query

A
Anonymous
October 16, 2015

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])
smacdonald2008
smacdonald2008
New Participant
October 16, 2015

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. 

A
Anonymous
October 16, 2015

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

smacdonald2008
smacdonald2008
New Participant
October 16, 2015

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. 

Lokesh_Shivalingaiah
Lokesh_Shivalingaiah
New Participant
October 16, 2015

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

A
Anonymous
October 16, 2015

Thank you.

Show more replies
Terms & ConditionsAccessibility statement

Loading footer...