Querying large documents is extremly slow from SDK, but fast from web-ui or cbq

SQL++
,
4

Query using ARRAY index and part of the ANY clause using the parameters. In that situation it can’t use implicit covering index MB-33009

After you have query parameters replace the paramaters in actual statement and execute better performance.
Also you can try the following index ( Index has ALL to cover through UNNEST) and query

CREATE INDEX blacklisted_passwords_idx
ON `blacklist`(ALL ARRAY LOWER(pw) FOR pw IN passwords END)
WHERE type="password-blacklist";

SELECT RAW COUNT( DISTINCT META(b).id ) FROM `blacklist` AS b
USE INDEX (blacklisted_passwords_idx)
UNNEST b.passwords AS pw
WHERE b.type="password-blacklist" AND LOWER(pw) = LOWER($password) ;

Please checkout reply from this post Query with embedded parameters 10x faster than parameterized query

1 Like