content fragments dam:Asset dam:AssetContent traversing queries optimize

[sreenu539]

I have tried below two queries to return content fragments below certain dam path.

On explain query tool I see queries "no indexes used". 

query1:
/jcr:root/content/dam/test/footnote-1//element(*, dam:AssetContent)

query2:
/jcr:root/content/dam/test/footnote-1//element(*, dam:Asset)

can some one suggest what is wrong with above two queries, why not any index used ( there is dam:Asset index out of the box and I was expecting to be used /oak:index/damAssetLucene )

I might be missing something here. logs on query

org.apache.jackrabbit.oak.query.QueryImpl Traversal query (query without index): select [jcr:path], [jcr:score], * from [dam:AssetContent] as a where isdescendantnode(a, '/content/dam/test/footnote-1') /* xpath: /jcr:root/content/dam/test/footnote-1//element(*, dam:AssetContent) */ union select [jcr:path], [jcr:score], * from [dam:AssetContent] as a where isdescendantnode(a, '/content/dam/test/footnote-2') /* xpath: /jcr:root/content/dam/test/footnote-2//element(*, dam:AssetContent) */; consider creating an index

Thanks,

Sri

[Saravanan_Dharmaraj]

@sreenu539 Please check the related post which suggests how to troubleshoot

https://experienceleaguecommunities.adobe.com/t5/adobe-experience-manager/issue-with-indexes-with-gr...

GraphQL queries too converted into SQL2 internally.

[DPrakashRaj · Accepted solution]

The log is only for 

dam:AssetContent

Can you share the log for 

dam:Asset

as well. Query looks good to me. If it’s not working for one of the instance can you check on another server also can you check this query after reindexing the  /oak:index/damAssetLucene by making the reindex property true from crx/de and wait this reindexing to complete before querying