Expand my Community achievements bar.

Guidelines for the Responsible Use of Generative AI in the Experience Cloud Community.
SOLVED

Indexing strategy for JCR-SQL2 statement with "OR" operator

Avatar

Level 1

Anyone has any experience creating AEM oak index for two properties that are combined in an OR statement? I have created oak indexes for both properties (lastReviewed is a custom property) but none of them are being used for this statement:

SELECT * FROM [cq:PageContent] AS s WHERE ISDESCENDANTNODE([/content]) AND ((s.[cq:lastReplicated] IS NOT NULL AND s.[cq:lastReplicated] <= CAST('2015-11-25T14:15:00.363+08:00' AS DATE)) OR (s.[lastReviewed] IS NOT NULL AND s.[lastReviewed] <= CAST('2015-11-25T14:15:00.363+08:00' AS DATE)))

After searching on the internet, seems like OR statements make a SQL statement not use index. From this page: http://stackoverflow.com/questions/13894064/mysql-or-operator-not-using-index
"An OR expression can't be used to look something up in an index"
Tried to split up the statement into two select statements combined with UNION instead which uses the index. My preliminary test seems to show that the UNION method is alot slower than the original version.

Here is what the new SQL statement look like:

SELECT * FROM [cq:PageContent] AS s WHERE ISDESCENDANTNODE([/content]) AND (s.[cq:lastReplicated] IS NOT NULL AND s.[cq:lastReplicated] <= CAST('2015-11-25T14:15:00.363+08:00' AS DATE))
UNION
SELECT * FROM [cq:PageContent] AS s WHERE ISDESCENDANTNODE([/content]) AND (s.[lastReviewed] IS NOT NULL AND s.[lastReviewed] <= CAST('2015-11-25T14:15:00.363+08:00' AS DATE))

 

Anyone knows of a better idea of solving this? Any help would be appreciated.

1 Accepted Solution

Avatar

Correct answer by
Administrator
5 Replies

Avatar

Level 9

Hi Danny Suyanto,

I was having a hard time implementing jcr_sql queries, I preferred Query Builder API for make rich query. Here is the basic article which could help you.

https://docs.adobe.com/docs/en/aem/6-1/develop/search/querybuilder-api.html

---

Jitendra

Avatar

Correct answer by
Administrator

Avatar

Level 1

Hi all,

Thank you for the replies. However, these are not what I am really looking for. The issue here is not about building the query, but how to make it such that it uses index for performance reason. What I am trying to find out is how do I make my query uses index since my query uses "OR" operator. Worst case scenario, I might use UNION to combine 2 SELECT statements that use index and see if that is faster for bigger data/content.

Avatar

Level 9

Danny,

Here is the doc which can help you to create an efficient index. And hopefully, it could solve your problem.

https://docs.adobe.com/docs/en/aem/6-1/administer/operations/operations-dashboard.html#par_title_109...

--

jitendra