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.
Solved! Go to 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
I hope this would be some help to you.
Thanks and Regards
Kautuk Sahni
Less relevant article :- http://labs.6dglobal.com/blog/2014-10-07/9-jcr-sql-2-queries-every-aem-dev-should-know/
Views
Replies
Total Likes
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
Views
Replies
Total Likes
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
I hope this would be some help to you.
Thanks and Regards
Kautuk Sahni
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.
Views
Replies
Total Likes
Danny,
Here is the doc which can help you to create an efficient index. And hopefully, it could solve your problem.
--
jitendra
Views
Replies
Total Likes
Views
Likes
Replies
Views
Likes
Replies
Views
Likes
Replies