select idCampaign , isRead , msg , server_date , toUser , type , user_date , usersInChat ’ +
’ from test where tbl=“msgs” And ’ +
’ every userId in usersInChat satisfies userId in [’+from+’,’+to+’] end order by server_date’
I need to define index for this line, cause the msgs are load very slow (in localhost).
I tried to defined index in this way (without success):
CREATE INDEX ix1 ON selfiecash( DISTINCT ARRAY userId FOR userId IN usersInChat END)’
Please help, thanks.
ANY or SOME is TRUE if the collection is non-empty and at least one element matches.
EVERY is TRUE if the collection is empty, or if the collection is non-empty and every element matches.
ANY AND EVERY or SOME AND EVERY is TRUE if the collection is non-empty and every element matches.
As EVERY on empty collection is True. as arrayindex will not index on empty collection, It can’t use ARRAY Indexing.
Array Indexing can be used when non-empty collection is true, Only on ANY or ANY AND EVERY can pick array index.
If it is okay you can try ANY AND EVERY
I modify the select query to any and every …
But it still slow, for 20 items it take to load 2 sec and it on my localhost (without any latency)
What can be the cause? maybe the create index is not good?
Do you mean query takes 2 seconds, You also have order by. Modify the index as follows. If still have issue post the EXPLAIN
CREATE INDEX ix1 ON selfiecash( DISTINCT ARRAY userId FOR userId IN usersInChat END) WHERE tbl=“msgs”;
I also tried to remove the order by to understand what is the problem.
It still slow the same result time.
The explain:
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “DistinctScan”,
“scan”: {
"#operator": “IndexScan2”,
“index”: “ix22”,
“index_id”: “404e08b98a21a99e”,
“index_projection”: {
“primary_key”: true
},
“keyspace”: “test”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: “1”,
“inclusion”: 3,
“low”: “1”
}
]
},
{
“exact”: true,
“range”: [
{
“high”: “2”,
“inclusion”: 3,
“low”: “2”
}
]
}
],
“using”: “gsi”
}
},
{
"#operator": “Fetch”,
“keyspace”: “test”,
“namespace”: “default”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Filter”,
“condition”: “(((test.tbl) = “msgs”) and any and every userId in (test.usersInChat) satisfies (userId in [1, 2]) end)”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“expr”: “(test.idCampaign)”
},
{
“expr”: “(test.isRead)”
},
{
“expr”: “(test.msg)”
},
{
“expr”: “(test.server_date)”
},
{
“expr”: “(test.toUser)”
},
{
“expr”: “(test.type)”
},
{
“expr”: “(test.user_date)”
},
{
“expr”: “(test.usersInChat)”
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
“text”: “select idCampaign , isRead , msg , server_date , toUser , type , user_date , usersInChat from test where tbl=“msgs” And Any And Every userId in usersInChat satisfies userId in [1,2] end”
}
Plan looks good. ix22 definition is same as this.
CREATE INDEX ix22 ON selfiecash( DISTINCT ARRAY userId FOR userId IN usersInChat END) WHERE tbl=“msgs”;
It looks like you are using CB 5.0.0. In cbq shell
\set -profile timings;
select idCampaign , isRead , msg , server_date , toUser , type , user_date , usersInChat from test where tbl=“msgs” And Any And Every userId in usersInChat satisfies userId in [1,2] end;
Check “#stats” of each operator and see where it is taking time.
some times i also get this message:
Error: An unknown N1QL error occured. This is usually related to an out-of-memory condition. select * from test where tbl=“myCampaign” And id=1500420564499 And idUser=2.
Why?
I tried to connect to cbq and run the query, but i get error:
“User does not have credentials to access privilege cluster.bucket[test].n1ql.select!execute. Add role Query Select [test] to allow the query to run.”
I logged as admin and i am stuck.
What do i need to do?
Result:
cbq> select * from test where tbl=“myCampaign”;
{
“requestID”: “60fe818b-c355-4c93-a41a-06fce29fa872”,
“signature”: {
"": ""
},
“results”: [
],
“errors”: [
{
“code”: 13014,
“msg”: “User does not have credentials to access privilege cluster.bucket[test].n1ql.select!execute. Add role Query Select [test] to allow the query to run.”
}
],
“status”: “stopped”,
“metrics”: {
“elapsedTime”: “3.587837ms”,
“executionTime”: “3.552323ms”,
“resultCount”: 0,
“resultSize”: 0,
“errorCount”: 1
}
}
cbq shell login using user and password that used in other place.
cbq -u admin -p password. If that also fails you may have to add privileges to user you are going to use.
Check proper index for out of memory otherwise query might be fetching lot of data and eliminating later.
OR
If you are using Query Workbench
Add Privileges in Query Workbench
Security Tab—> Add user
Edit User and privileges.
Hi thanks for your responses,
I understand my problem, everything was good with the index of array, the problem found in other query that slow the request call.
I have query with OR
The query:
select * from test where tbl=“myCampaign” And id=1500420564499 And (idUser=2 Or idSocial=2)
I did the explain and i see that it use in index:
CREATE INDEX index_idUser on test(idUser) using GSI
this index is not fast enough.
How can i improve the index to use the OR or to do fast in other way?
CREATE INDEX ix1 ON test(id,idUser) WHERE tbl = “myCampaign”;
CREATE INDEX ix2 ON test(id,idSocial) WHERE tbl = “myCampaign”;
Do EXPLAIN select * from test where tbl=“myCampaign” And id=1500420564499 And (idUser=2 Or idSocial=2);
It should have used both indexes and did UnionScan
If i will do replication, do i need to modify the index with addition values?
Hi @deepkaran.salooja will be able to answer the replication question
Do i need to wait for response here from @deepkaran.salooja , or send him direct message?
He will post the response here.
@roycouch, to create a replica index, you need to have the exact same definition. The system will automatically recognize the index as replica based on its definition.
Also, you can try to increase memory quota assigned to Index Service(UI->Settings) to see if it helps performance.
Are you looking WITH {“num_replica”: 2}, In 5.0.0 @deepkaran.salooja
https://developer.couchbase.com/documentation/server/5.0/indexes/index-replication.html
With 5.0.0, num_replica is an option. For earlier releases, creating multiple indexes with same definition is the only option.
So you mean that the index know to work auto with the other replica, with the simple definition, for example:
CREATE INDEX index_idUser on test(idUser) using GSI
Can i get some response please?