Jackrabbit Oak Indexing

I'm executing this JCR SQL2 query:

SELECT * FROM [my:type] AS n
WHERE NAME(n) LIKE 'node_name_prefix.%'
AND n.deleted = CAST('false' AS BOOLEAN)
AND CONTAINS(n.user, '1f12f97d-6516-48b9-ae75-47d17ef6877f') 
AND CONTAINS(n.state, 'executing')
OR CONTAINS(n.state, 'done')

And get the warning that I should create indices:

Traversed 1000 nodes with filter Filter(query=SELECT * FROM [my:type] AS n
WHERE NAME(n) LIKE 'node_name_prefix.%' AND n.deleted = CAST('false' AS 
BOOLEAN) AND CONTAINS(n.user, '1f12f97d-6516-48b9-ae75-47d17ef6877f') AND 
CONTAINS(n.state, 'executing') OR CONTAINS(n.state, 'done') 
fullText=user:"1f12f97d-6516-48b9-ae75-47d17ef6877f" (state:"executing" OR 
state:"done"), path=*, property=[:localname=[(node_name_prefix.%..], 
deleted=[false], state=[is not null]]); consider creating an index or 
changing the query

Which I did, like this:

NodeBuilder rootBuilder = this.segmentNodeStore.getRoot().builder();
NodeBuilder index = IndexUtils.getOrCreateOakIndex(rootBuilder);
NodeBuilder childNode = index.getChildNode(propertyName);
IndexUtils.createIndexDefinition(index, propertyName, true, uniqueValue, ImmutableList.of(propertyName), null);
CommitHook hook = new CompositeHook(new ConflictHook(JcrConflictHandler.createJcrConflictHandler()), new EditorHook(new ConflictValidatorProvider()));

try
{
  this.segmentNodeStore.merge(rootBuilder, hook, CommitInfo.EMPTY);
}
catch(CommitFailedException ex)
{
  throw new IOException(ex);
}

Where propertyName was one of these Strings: deleted, state, jcr:localname, jcr:path, jcr:property, jcr:fullText, property, localname, path, user, fullText

But I still get that warning, which I guess means that my indices won't be used. When I print out all available indices I get the following:

Available indices: deleted, repMembers, counter, state, jcr:localname, jcr:path, acPrincipalName, jcr:property, jcr:fullText, uuid, property, localname, nodetype, reference, principalName, path, user, authorizableId, fullText

So it seems my indices are created at the same place where some jcr default indices already exist, like nodetype, acPrincipalName, reference, repMembers, authorizableId and counter

Any hints what am I doing wrong here? I just want to ensure that the queries are as fast as they can be, so I really would like to know how to create the indices that they will be used by oak.

I'm using oak version 1.5.12 and my repository is instantiated like this:

this.fileStore = FileStore.builder(new File("/path/to/my/repo")).withCacheSize(512).withMemoryMapping(true).build();
this.segmentNodeStore = SegmentNodeStore.builder(this.fileStore).build();
this.repository = new Jcr(new Oak(this.segmentNodeStore)).with(qes).withAsyncIndexing().createRepository();

Solution

According to your query above, you should create a lucene property index under /oak:index as follows:

<?xml version="1.0" encoding="UTF-8"?>
<jcr:root xmlns:my="TYPE THE MY TYPE DEFINITION URI" xmlns:jcr="http://www.jcp.org/jcr/1.0" xmlns:nt="http://www.jcp.org/jcr/nt/1.0"
    jcr:primaryType="oak:Unstructured"
    indexDescription="My Index Defition"
    compatVersion="{Long}2" 
    type="lucene"
    async="async">
    <indexRules jcr:primaryType="nt:unstructured">
      <my:type 
        jcr:primaryType="nt:unstructured"
        indexNodeName="{Boolean}true">
        <properties jcr:primaryType="nt:unstructured">
            <deleted name="deleted" propertyIndex="{Boolean}true" type="Boolean" jcr:primaryType="nt:unstructured"/>
            <user name="user" analyzed="{Boolean}true" nodeScopeIndex="{Boolean}true" jcr:primaryType="nt:unstructured"/>
            <state name="state" analyzed="{Boolean}true" nodeScopeIndex="{Boolean}true" jcr:primaryType="nt:unstructured"/>
        </properties>
      </my:type>
    </indexRules>
</jcr:root>

Explanation:

+ myCustomIndex
   - type = "lucene" -> tells that your are defining lucene index
   - async = "async" -> should be alsways set to "async" according the docs
   + indexRules -> defines the rules for this index
     + my:type -> defines the index rules for your custom node type
       - indexNodeName = "true" -> indexes the node name and make "like" queries possible
       + properties -> the index rules for the properties on your my:type node
         + deleted
           - name = "deleted" -> property name
           - propertyIndex = "true" -> controls if this property is used for equality conditions
           - type = "Boolean" -> property type
         + user
           - name = "user" -> property name
           - analyzed = "true" -> when used as part of contains
           - nodeScopeIndex = "Boolean" -> control whether the value of a property should be part of fulltext index
         + state
           - name = "state" -> property name
           - analyzed = "true" -> when used as part of contains
           - nodeScopeIndex = "Boolean" -> control whether the value of a property should be part of fulltext index