Creating an index for a query that uses LOWER

SQL++
1

Hi all,

I have created the following index for a N1QL query:

CREATE INDEX index_write_userrelation_relationkeys ON write (relationKeys) WHERE docType = 'userRelation' USING GSI

The n1ql query is:

SELECT relationKeys FROM write 
WHERE docType = 'userRelation' 
AND ANY relationKey in relationKeys SATISFIES LOWER(relationKey) = $1 
END;

And a typical document of type userRelation is:

[
  {
    "write": {
      "createdDate": 1492026190551,
      "docType": "userRelation",
      "groupMemberships": [],
      "id": "000994f8-edc5-472c-926b-e2d645295f22",
      "lastModifiedDate": 1492026213996,
      "relationKeys": [
        "pax-booking-no_DJ9C4C02",
        "username_Testing@testing.com",
        "uiid_xYcfgdesTYUIIIIII",
        "ccn_8545149777"
      ],
    }
]

I am concerned that maybe the index being created is not compatible with the query that appears designed to take advantage of the index. The query uses “LOWER(relationKey)”, and the index is just based on “relationKeys”, since the documents are indexed by NON-lower-relationKeys, and the query is based on "LOWER(relationKey).

  1. Is this a correct assumption or do I need to change the index?

  2. If I need to change the index, could you give me some pointers as to what I need to change?

  3. How can you test if the index is working as expected?

Any help would be greatly appreciated.

Regards,
Damian.

2

The following is Index definition. Query uses covered index.

CREATE INDEX ix20 ON default (DISTINCT ARRAY LOWER(relationKey) FOR relationKey IN relationKeys END, relationKeys) WHERE docType = ‘userRelation’;

SELECT relationKeys FROM default
WHERE docType = 'userRelation' AND ANY relationKey IN relationKeys SATISFIES LOWER(relationKey) = $1 END;

You can check the query plan and what index is using by adding EXPLAIN in front like below.

EXPLAIN SELECT relationKeys FROM default
    WHERE docType = 'userRelation' AND ANY relationKey IN relationKeys SATISFIES LOWER(relationKey) = $1 END;

NOTE: relationKeys array is too big Indexer may skip indexing the key and log the document in indexer.log. If that is the case remove second index key (like below) and query does non-covered query and document Fetch.

CREATE INDEX ix20 ON default (DISTINCT ARRAY LOWER(relationKey) FOR relationKey IN relationKeys END) WHERE docType = 'userRelation';

1 Like
3

Hi vsr1,

Thanks for your prompt response.

Sorry, but I do not understand what you mean by:

NOTE: relationKeys array is too bit Indexer may skip indexing the key and log the document in indexer.log. If that is the case remove second index key (like below) and query does non-covered query and document Fetch.

  1. What is relationKeys is too bit?

  2. When will the indexer skips indexing the key? Is it when you perform the query?

  3. Where is indexer.log located?

Regards,
Damian.

4

Sorry typo.

  1. relationKeys array size is too big. Please the see the Limitations section of link for details https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/indexing-arrays.html
  2. During indexing, If skipped during indexing query will not find.
  3. https://developer.couchbase.com/documentation/server/4.0/troubleshooting/troubleshooting-logs.html
1 Like
5

Hi vsr1,

Thank you for all your feedback.

Just one last question

I am using Couchbase Version: 4.5.1-2844 Enterprise Edition (build-2844)

Will the covered index that you outlined in your response work with this version of Couchbase?
If not, what index should I use?

Regards,
Damian.

6

Covered index should work in your case

1 Like
7

Hi vsr1,

Thank you for all of your help.

Regards,
Damian.