AEM QueryBuilder - SQL 2 propertyIndex is not working

[vijays80591732]

Hello all,

Below is the query that i'm using to get certain paths that is available from productData property under a node. I'm trying to have a propertyIndex for this property so that i can reduce my traverse time during query execution.

Map<String, String> map = new HashMap<String, String>();

// create query description as hash map (simplest way, same as form post)

map.put("path", "/content/myproject");

map.put("type", "nt:unstructured");

map.put("1_property", "productData");

map.put("1_property.value", "%"+productId);

map.put("1_property.operation", "like");

Below is the propertyIndex that i have created under this path /oak:index/myIndex

<myIndex

       jcr:primaryType="oak:QueryIndexDefinition"

       includedPaths="[/content/myproject/]"

       propertyNames="{Name}[productData]"

       reindex="{Boolean}false"

       reindexCount="{Long}7"

       type="property"/>

I'm trying to check this indexing using this sql-2 query but by someway its not getting indexed. Kindly suggest me.

select * from [nt:unstructured] as a where [productData] like '%_content_myproject_aplivejajp-'

Arun Patidar smacdonald2008​ gauravb41175071​ deepak3344​ Ratna Kumar​

[Gaurav-Behl · Accepted solution]

Could you share more details when you say 'property index is not working'? Does it get picked up/applied to query in explain plan?

Does 'productId' substitute to '_content_myproject_aplivejajp-'?

You may add more restrictions to the definition to reduce the resultset and test  - add declaringNodeTypes and valuePattern, if applicable

I just tested with following query with zero restrictions on index and it worked fine when I mention '%mma' or 'E%ma' but not when I mention '%m or '%mm' -

type=nt:unstructured

path=/content

p.limit=-1

1_property=email

1_property.value=%mma

1_property.operation=like

key value pair ---  email=Emma

Indexes Used

myEnsureIndex

Execution Time

Total time: 1 ms

• Query execution time: 1 ms
• Get nodes time: 0 ms
• Result node count time: 0 ms
• Number of nodes in result: 1

Execution Plan

[nt:unstructured] as [a] /* property myEnsureIndex IS NOT NULL where ([a].[email] like '%mma') and (isdescendantnode([a], [/content])) */

Logs

Parsing xpath statement: explain /jcr:root/content//element(*, nt:unstructured)[(jcr:like(@email, '%mma'))] Execute xpath / explain /jcr:root/content//element(*, nt:unstructured)[(jcr:like(@email, '%mma'))] XPath > SQL2: explain select [jcr:path], [jcr:score], * from [nt:unstructured] as a where [email] like '%mma' and isdescendantnode(a, '/content') /* xpath: /jcr:root/content//element(*, nt:unstructured)[(jcr:like(@email, '%mma'))] */ Literal used Attempting optimisation cost using filter Filter(query=explain select [jcr:path], [jcr:score], * from [nt:unstructured] as a where [email] like '%mma' and isdescendantnode(a, '/content') /* xpath: /jcr:root/content//element(*, nt:unstructured)[(jcr:like(@email, '%mma'))] */, path=/content//*, property=[email=[(%mma..]]) cost for reference is Infinity property cost for myEnsureIndex is 3.0 property cost for myNodeApps is 19.0 cost for property is 3.0