Index not being used when used in parameterized queries in golang

Go SDK
, ,
1

Hi We are using couchbase 6.6.0 community edition with gocb 2.3.1

We have a simple query
SELECT * FROM mydb use index(user_googleid_index) WHERE type = ‘User’ AND googleUserId = “value”

We are using following index
CREATE INDEX user_googleid_index ON vidya ( googleUserId ) WHERE ( type = “User”)

When we fire query from couchbase admin console, it returns in just 4.8ms, index is hit as shown in query explain

When we fire from golang api code, it takes too long to respond and index is not hit as shown in debug query log

In API code, we are using parameterised query as following

const query = “SELECT *” +
" FROM mydb use index(user_googleid_index)" +
" WHERE type = ‘User’ AND googleUserId = $googleUserId"

params := make(map[string]interface{})
params["googleUserId"] = googleUserId

rows, err := bucket.QueryWithParam(query, params, gocb.QueryScanConsistencyNotBounded)

In Api if don’t use parameterized query index gets hit.

Using parameterized query is important to ensure security. Please suggest how to resolve this issue.

2

Just observed, restarting couchbase server , solves it. Probably it has something to do prepared statement caching. Is there way, we don’t have to restart couchbase as we keep having api updates and we don’t want t restart database server for api updates

3

Checkout PREPARE | Couchbase Docs
Monitor Queries | Couchbase Docs

1 Like
4

Thanks. Deleting prepared statement from following command solves the issue

DELETE FROM system:prepareds WHERE name = “p1”;

1 Like