Using array index to speed up update query

SQL++
1

Hi there. I am executing next query looks like the follows:

UPDATE `travel-sample`
SET v.utc = '10:13:01'
FOR v IN schedule WHEN v.flight = 'AF198' END
WHERE type = 'route'

Unfortunately I don’t know route ids in advance. And it’s very slow. On my machine with local cluster (one couchbase 4.5.1-CE node) it takes something aroung 19sec.
So, I decided to use array index and created next one:

CREATE INDEX idx_routes_schedule_flight ON `travel-sample` ( DISTINCT ARRAY v.flight FOR v IN schedule END ) 
WHERE type = 'route';

But query plan does not use it. As far as I understand from documentation array indexes only work for where-clauses, right? Or I can use it somehow for FOR-IN-WHEN clause inside SET operation?

2 
UPDATE `travel-sample`
SET v.utc = '10:13:01'
FOR v IN schedule WHEN v.flight = 'AF198' END
WHERE type = 'route' AND ANY v IN schedule SATISFIES  v.flight = 'AF198' END;

You need to eliminate the documents that doesn’t qualify through WHERE clause.

Example: if schedule doesn’t have flight = ‘AF198’ with out ANY condition the documents qualify for update and set value expression findings nothing qualifies to change this result in mutation without any value change and you want avoid this.

1 Like