Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn More

View all

Sign in to view all badges

AEM QueryBuilder - SQL 2 propertyIndex is not working

Avatar

Avatar
Validate 1
Level 2
vijays80591732
Level 2

Likes

6 likes

Total Posts

56 posts

Correct Reply

4 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 1
Affirm 3
View profile

Avatar
Validate 1
Level 2
vijays80591732
Level 2

Likes

6 likes

Total Posts

56 posts

Correct Reply

4 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 1
Affirm 3
View profile
vijays80591732
Level 2

18-02-2019

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 smacdonald2008gauravb41175071deepak3344Ratna Kumar

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar
Give back 300
MVP
Gaurav-Behl
MVP

Likes

243 likes

Total Posts

1,145 posts

Correct Reply

281 solutions
Top badges earned
Give back 300
Give Back 50
Give Back 5
Give Back 3
Give Back 25
View profile

Avatar
Give back 300
MVP
Gaurav-Behl
MVP

Likes

243 likes

Total Posts

1,145 posts

Correct Reply

281 solutions
Top badges earned
Give back 300
Give Back 50
Give Back 5
Give Back 3
Give Back 25
View profile
Gaurav-Behl
MVP

18-02-2019

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


Answers (0)