CODEWITHSUNDEEP
couchbasesql++spring-data-couchbase
Lho Ben
Lho Ben

Reputation: 2149

Indexes for couchbase query

Could you please suggest possible couchbase indexes for this query and how you drive your choices?

SELECT Count(*) AS count 
FROM   `ORDER` 
WHERE  `_class` = "com.lbk.entities.OrderEntity" 
AND    ( 
              lower(buyer.contact.firstname) LIKE '%aziz%' 
       OR     lower(buyer.contact.lastname) LIKE '%aziz%' 
       OR     ANY communicationchannel IN buyer.contact.communicationchannel satisfies ( communicationchannel.communicationchannelcode = 'EMAIL'
       AND    communicationchannel.communicationvalue = NULL ) 
END ) 
AND    ordertypecode = '220' 
AND    ( 
              ordercategory != 'EXCLUDED_CAT' 
       OR     ordercategory IS NOT valued ) 
AND    creationdatetime IN 
       ( 
                SELECT   raw max(o2.creationdatetime) 
                FROM     `ORDER` o2 
                WHERE    ( 
                                  lower(o2.buyer.contact.firstname) LIKE '%aziz%' 
                         OR       lower(o2.buyer.contact.lastname) LIKE '%aziz%' 
                         OR       ANY communicationchannel IN o2.buyer.contact.communicationchannel satisfies ( communicationchannel.communicationchannelcode = 'EMAIL'
                         AND      communicationchannel.communicationvalue = NULL ) 
                END ) 
                AND      ANY communicationchannel IN o2.buyer.contact.communicationchannel satisfies ( communicationchannel.communicationchannelcode = 'EMAIL'
                AND      communicationchannel.communicationvalue IS NOT NULL )  END 
                AND      o2.ordertypecode = '220' 
                AND      ( 
                                  o2.ordercategory != 'EXCLUDED_CAT' 
                         OR       o2.ordercategory IS NOT valued) 
                GROUP BY ( array item.communicationvalue FOR item IN o2.buyer.contact.communicationchannel WHEN item.communicationchannelcode = 'EMAIL'
                END )
            )

i have created this index which is hitted by the query :

CREATE INDEX `idx_customer` ON 
`order`(`_class`, ((`buyer`.`contact`).`firstname`), ((`buyer`.`contact`).`lastname`), (DISTINCT (array
(`aoc`.`communicationvalue`) FOR `aoc` IN ((`buyer`.`contact`).`communicationchannel`) WHEN 
((`aoc`.`communicationchannelcode`) = "EMAIL") end)))

but my performances are poor and corresponding to other responses it is mainely due to my poor index design.

the explanition of my query is :

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "UnionScan",
        "scans": [
          {
            "#operator": "IntersectScan",
            "scans": [
              {
                "#operator": "IndexScan3",
                "index": "idx_customer1",
                "index_id": "d1463e49b12fcd45",
                "index_projection": {
                  "primary_key": true
                },
                "keyspace": "order",
                "namespace": "default",
                "spans": [
                  {
                    "range": [
                      {
                        "high": "\"220\"",
                        "inclusion": 3,
                        "low": "\"220\""
                      },
                      {
                        "inclusion": 0,
                        "low": "null"
                      }
                    ]
                  }
                ],
                "using": "gsi"
              },
              {
                "#operator": "DistinctScan",
                "scan": {
                  "#operator": "IndexScan3",
                  "index": "idx_customer",
                  "index_id": "2132a2f8632e76f3",
                  "index_projection": {
                    "primary_key": true
                  },
                  "keyspace": "order",
                  "namespace": "default",
                  "spans": [
                    {
                      "range": [
                        {
                          "high": "\"com.lbk.entities.OrderEntity\"",
                          "inclusion": 3,
                          "low": "\"com.lbk.entities.OrderEntity\""
                        },
                        {
                          "inclusion": 0,
                          "low": "null"
                        }
                      ]
                    }
                  ],
                  "using": "gsi"
                }
              }
            ]
          },
          {
            "#operator": "IndexScan3",
            "index": "idx_customer1",
            "index_id": "d1463e49b12fcd45",
            "index_projection": {
              "primary_key": true
            },
            "keyspace": "order",
            "namespace": "default",
            "spans": [
              {
                "range": [
                  {
                    "high": "\"220\"",
                    "inclusion": 3,
                    "low": "\"220\""
                  },
                  {
                    "inclusion": 0,
                    "low": "null"
                  }
                ]
              }
            ],
            "using": "gsi"
          }
        ]
      },
      {
        "#operator": "Fetch",
        "keyspace": "order",
        "namespace": "default"
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "Filter",
              "condition": "((((((`order`.`_class`) = \"com.lbk.entities.OrderEntity\") and (((lower((((`order`.`buyer`).`contact`).`firstName`)) like \"%aziz%\") or (lower((((`order`.`buyer`).`contact`).`lastName`)) like \"%aziz%\")) or any `communicationChannel` in (((`order`.`buyer`).`contact`).`communicationChannel`) satisfies (((`communicationChannel`.`communicationChannelCode`) = \"EMAIL\") and ((`communicationChannel`.`communicationValue`) = null)) end)) and ((`order`.`orderTypeCode`) = \"220\")) and ((not ((`order`.`orderCategory`) = \"EXCLUDED_CAT\")) or ((`order`.`orderCategory`) is not valued))) and ((`order`.`creationDateTime`) in (select raw max((`O2`.`creationDateTime`)) from `order` as `O2` where ((((((lower((((`O2`.`buyer`).`contact`).`firstName`)) like \"%aziz%\") or (lower((((`O2`.`buyer`).`contact`).`lastName`)) like \"%aziz%\")) or any `communicationChannel` in (((`O2`.`buyer`).`contact`).`communicationChannel`) satisfies (((`communicationChannel`.`communicationChannelCode`) = \"EMAIL\") and ((`communicationChannel`.`communicationValue`) = null)) end) and any `communicationChannel` in (((`O2`.`buyer`).`contact`).`communicationChannel`) satisfies (((`communicationChannel`.`communicationChannelCode`) = \"EMAIL\") and ((`communicationChannel`.`communicationValue`) is not null)) end) and ((`O2`.`orderTypeCode`) = \"220\")) and ((not ((`O2`.`orderCategory`) = \"EXCLUDED_CAT\")) or ((`O2`.`orderCategory`) is not valued)))  group by array (`item`.`communicationValue`) for `item` in (((`O2`.`buyer`).`contact`).`communicationChannel`) when ((`item`.`communicationChannelCode`) = \"EMAIL\") end)))"
            },
            {
              "#operator": "InitialGroup",
              "aggregates": [
                "count(*)"
              ],
              "group_keys": []
            }
          ]
        }
      },
      {
        "#operator": "IntermediateGroup",
        "aggregates": [
          "count(*)"
        ],
        "group_keys": []
      },
      {
        "#operator": "FinalGroup",
        "aggregates": [
          "count(*)"
        ],
        "group_keys": []
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "InitialProject",
              "result_terms": [
                {
                  "as": "count",
                  "expr": "count(*)"
                }
              ]
            },
            {
              "#operator": "FinalProject"
            }
          ]
        }
      }
    ]
  },
  "text": "SELECT COUNT(*) AS count FROM `order` \nWHERE `_class` = \"com.lbk.entities.OrderEntity\" \nAND ( \nLOWER(buyer.contact.firstName) LIKE '%aziz%' OR LOWER(buyer.contact.lastName) LIKE '%aziz%' \nOR ANY communicationChannel IN buyer.contact.communicationChannel SATISFIES ( communicationChannel.communicationChannelCode = 'EMAIL' AND communicationChannel.communicationValue  = null ) END )  \nAND orderTypeCode = '220' \nAND (orderCategory != 'EXCLUDED_CAT' OR orderCategory is not valued ) \nAND creationDateTime in (select RAW max(O2.creationDateTime) \nfrom `order` O2 WHERE ( LOWER(O2.buyer.contact.firstName) \nLIKE '%aziz%' OR LOWER(O2.buyer.contact.lastName) LIKE '%aziz%' \nOR ANY communicationChannel IN O2.buyer.contact.communicationChannel SATISFIES ( communicationChannel.communicationChannelCode = 'EMAIL' AND communicationChannel.communicationValue  = null ) END )  \nAND ANY communicationChannel IN O2.buyer.contact.communicationChannel SATISFIES ( communicationChannel.communicationChannelCode = 'EMAIL' AND communicationChannel.communicationValue is not null ) END \nAND O2.orderTypeCode = '220' \nAND (O2.orderCategory != 'EXCLUDED_CAT'\nOR O2.orderCategory is not valued) \ngroup by ( ARRAY item.communicationValue FOR item IN O2.buyer.contact.communicationChannel WHEN item.communicationChannelCode = 'EMAIL' END ))"
}

When this query is executed in query monitor, it takes (2 seconds even if i have a little number of documents ~2000 ) :

elapsed: 1.93s | execution: 11.93s | count: 1 | size: 34

When executed from my spring boot application using spring data, it take double time (4 seconds).

Thanks for your help

Upvotes: 1

Views: 254

Answers (1)

vsr
vsr

Reputation: 7414

It looks to me you are scanning the ORDER twice. Make sure if query using the ix1 only. If not specify USE INDEX

CREATE INDEX ix1 ON (ordertypecode, buyer.contact.firstname, buyer.contact.lastname, creationdatetime, ordercategory,buyer.contact.communicationchannel)
WHERE `_class` = "com.lbk.entities.OrderEntity";

SELECT SUM(o.cnt) AS count
FROM ( SELECT MAX([o1.creationdatetime,o1.cnt])[1] AS cnt
       FROM (SELECT   acomval, o2.creationdatetime, COUNT(1) AS cnt
             FROM  `ORDER` o2
             LET acomval = (ARRAY ch.communicationvalue FOR ch IN o2.buyer.contact.communicationchannel
                            WHEN ch.communicationchannelcode = 'EMAIL' END )
             WHERE (LOWER(o2.buyer.contact.firstname) LIKE '%aziz%'
                       OR LOWER(o2.buyer.contact.lastname) LIKE '%aziz%'
                       OR ANY ch IN o2.buyer.contact.communicationchannel
                              SATISFIES ( ch.communicationchannelcode = 'EMAIL' AND ch.communicationvalue IS NULL)
                          END
                      )
                      AND ANY ch IN o2.buyer.contact.communicationchannel
                              SATISFIES (ch.communicationchannelcode = 'EMAIL' AND ch.communicationvalue IS NOT NULL)  END
                      AND  o2.`_class` = "com.lbk.entities.OrderEntity"
                      AND  o2.ordertypecode = '220'
                      AND ( o2.ordercategory != 'EXCLUDED_CAT' OR o2.ordercategory IS NOT VALUED)
             GROUP BY acomval, o2.creationdatetime) AS o1
       GROUP BY o1.acomval) AS o;

Upvotes: 2

Related Questions