Covering Index with performance using select query

SQL++
1

Hi All,

I have document structure like below:

{

“SURVEY_TYPE”:“FEEDBACK”,
“MENTION_TIME”:“2008-06-07 05:30:00”,
“ID”:21206,
“ACTIVE_FLAG”:true,
“SOURCE_ID”:100115,
“CONTENTS”:[

  { 
     "ACTIVE_FLAG":true, 
     "PREDEFINED":false, 
     "MD5":"911d10c91c6c80afb0a1a4d4ab786d24", 
     "QUESTION_ID":1000000000, 
     "STATUS":"P", 
     "PUBLISHING_API_STATUS":"N", 
     "OPTION_ID":1, 
     "MODIFIED_TIME":"2015-08-18 10:31:08", 
     "SENTIMENTS":[ 
        { 
           "GROUP_ID":"npaaru@bfountain.com_1394536948561", 
           "CONTENT_ID":2175967,
           "CATEGORY_ID":4015, 
           "ACTIVE_FLAG":true, 
           "SCORE":1, 
          
        } 
       
     ], 
    
     "NLP_FLAG":true, 
     "CREATED_TIME":"2014-03-11 09:18:29" 
  }

],
“REVIEW_COUNT”:1,
“LOCATION_ID”:0,
“OWNER_POST_FLAG”:false,
“PERSON_ID”:11489,
“RECOMMEND_FLAG”:true,
“HARVESTER_ID”:74177,

}

so i’ve the requirement to get the average score in minimum time using indexing,so i go through the docs having covering index example and i wrote below index:

CREATE INDEX idx_score ON default
((DISTINCT ARRAY
(DISTINCT ARRAY s.SCORE AND s.CATEGORY_ID FOR s IN c.SENTIMENTS END)
FOR c IN CONTENTS END),PERSON_ID,SURVEY_TYPE,LOCATION_ID,SOURCE_ID,CONTENTS)
WHERE MENTION_TIME>=‘2001-01-01’;

then i use below SELECT query to fetch the value using index “idx_score”,

EXPLAIN SELECT AVG( s.SCORE)
FROM default t
UNNEST t.CONTENTS c
UNNEST c.SENTIMENTS s
WHERE t.PERSON_ID=11489 AND s.CATEGORY_ID IN [4015,4015,4026,4034]and t.MENTION_TIME >=‘2001-01-01’ and t.MENTION_TIME<‘2016-03-07’ ;

BUT EXPLAIN SHOW BELOW:
{
“plan”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “PrimaryScan”,
“index”: “#primary”,
“keyspace”: “default”,
“namespace”: “default”,
“using”: “gsi”
},

WHICH USING PRIMARY INDEX KEY.

so i have below question here:
Q1: Is the problem i’m using ALIAS name ,like default t and using it.
how to make se select query to using this index.
Q2: how to use this if i have a alias name in select query
Q3: if i use any one of attributes mention in create index in my select query it ,should take this index in run time.

Any help will be appropriated.

2

Hi @rkumar, Use this example as the model and work your way on exploiting array indexing for UNNEST.

insert into b values("k1", {"x":1, "y": [10, 20, 30]});
create index ib1 on b(DISTINCT ARRAY v FOR v IN y END);
explain select v
from b unnest y v
where v = 10;

delete from b;
insert into b values("k1", {"x":1, "y": [ {"p":"a", "q": [11, 22,33]}, {"p":"b", "q": [44, 55,66]}]});
create index ib2 on b
   (DISTINCT ARRAY
         (DISTINCT ARRAY v2 FOR v2 IN v1.q END)
         FOR v1 IN y END );
explain select * from b where ANY v1 in y satisfies (any v2 in v1.q satisfies v2 = 44 end) end;
explain select * from b unnest y v1 unnest v1.q as v2 where v2= 44;
3

Hi @keshav_m,@geraldss

Thank you for this explanation,
The structure you created for example is very much similar to ours,that cool, but still its not working,i did this previously and it not working for me in all scenario, below what i did according what you suggested.

CREATE INDEX idx_score ON default
(DISTINCT ARRAY
(DISTINCT ARRAY s.SCORE AND s.CATEGORY_ID FOR s IN c.SENTIMENTS END)
FOR c IN CONTENTS END) ;

OR
CREATE INDEX idx_score ON default
(DISTINCT ARRAY
(DISTINCT ARRAY s FOR s IN c.SENTIMENTS END)
FOR c IN CONTENTS END) ;

below is the EXPLAIN:

explain select s.SCORE from default
unnest CONTENTS c
unnest c.SENTIMENTS as s
where s.CATEGORY_ID IN [4015];

{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “PrimaryScan”,
“index”: “#primary”,
“keyspace”: “default”,
“namespace”: “default”,
“using”: “gsi”
},

EVEN THUG IF I DID SELECT * WITH s.CATEGORY_ID=4015,GIVE THE SAME.
addon on this ,what if i want to include other attributes in create index,like

PERSON_ID,SOURCE_ID from parent array.

Can i create index like below:

CREATE INDEX idx_score_adv ON default (DISTINCT ARRAY (DISTINCT ARRAY s.SCORE AND s.CATEGORY_ID FOR s IN c.SENTIMENTS END)
FOR c IN CONTENTS END,PERSON_ID,SURVEY_TYPE,LOCATION_ID,SOURCE_ID) WHERE MENTION_TIME>=‘2001-01-01’;

Thank you.

4

Hi @rkumar, please post a FULL example document here.