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

[dannys23048637]

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.

[kautuk_sahni]

Less relevant article :- http://labs.6dglobal.com/blog/2014-10-07/9-jcr-sql-2-queries-every-aem-dev-should-know/

[Jitendra_S_Toma]

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

[kautuk_sahni · Accepted solution]

Hi 

Some useful articles :-

Link:- http://www.wemblog.com/2013/04/how-to-create-custom-query-predicate-in.html

Link:_ http://myadobecq.blogspot.in/2013/12/query-builder-in-brief.html

Link:- http://cq-ops.tumblr.com/post/23543240500/how-to-use-cqs-query-debugger-tool

Link:- http://labs.6dglobal.com/blog/2013-07-10/custom-predicateevaluators-or-how-i-learned-stop-worrying-a...

I hope this would be some help to you.

Thanks and Regards

Kautuk Sahni

[dannys23048637]

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.

[Jitendra_S_Toma]

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