Indexing for an Update Query

SQL++
1

UPDATE content SET categories = ARRAY a FOR a IN categories WHEN a != “veg” END
WHERE _type=“UserAsset” AND type IN [“IMAGE”,“AUDIO”,“VIDEO”,“SVG”,“FILE”,“PPT”] RETURNING categories

How to create a index for the above query… so that it will execute with in miliseconds ??

I have created index like
CREATE INDEX ix1d_UserAsset ON content(_type,type) WHERE (_type = “UserAsset”).
But its not working it seems.

2

Do you mean that it is not updating in milliseconds?

3

yes.it is not updating in miliseconds. Its taking about 20sec to execute.

4

I assume you want to update a very limited number of documents?
Could you show us the plan?

5 
CREATE INDEX ix1d_UserAsset ON content(type, DISTINCT ARRAY v FOR v IN categories END)
WHERE (_type = "UserAsset");

UPDATE content
SET categories = ARRAY a FOR a IN categories WHEN a != "veg" END
WHERE _type="UserAsset"
      AND type IN ["IMAGE","AUDIO","VIDEO","SVG","FILE","PPT"]
      AND ANY v IN categories SATISFIES v = "veg" END
RETURNING categories;
  1. UPDATE requires whole document Fetch
  2. mutation of document is controlled by WHERE clause. SET clause only decides what to set. If you have condition in set clause that can result in update same document with out any change. So you need to add that condition to WHERE clause avoid unnecessary mutation.