Performance diference between using KEYS o where indexed

Couchbase Server
1

Hello,
Which location method is used when I use this query:

Select ..
from bucket 
use KEYS 'country::' !! 'XX';

In this case the elapsed time is under milliseconds.
However in this case, after created a indexed

select  ...
from bucket a
where a.country='XX';

The elapsed time is about 30 seconds.

In which cases is recommended create buckets indexes?

Many Thanks
Arturo

2

USE KEYS is generally faster.

Can you post your index definition?

3

Hello,
The index definition is:
CREATE PRIMARY INDEX NDX_PRIMARY ON bucket1;

Thanks
Arturo

4

Ok. You have two options.

(1) You can use USE KEYS.

(2) You can use Couchbase 4.5, use the index, and post your EXPLAIN plan here.

5

Hello,
This is the access info,

cbq> SELECT a FROM couchmusic2 AS a WHERE a.countryCode = ‘FI’ ;
{
“requestID”: “579c9ec7-1aa8-4c57-847b-bf329846c36a”,
“signature”: {
“a”: “json”
},
“results”: [
{
“a”: {
“countryCode”: “FI”,
“gdp”: 49547,
“name”: “Finland”,
“population”: 5462936,
“region-number”: 154,
“type”: “country”,
“updated”: “2015-09-06T18:00:05”
}
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “38.207743933s”,
“executionTime”: “38.20768331s”,
“resultCount”: 1,
“resultSize”: 307
}
}

The elapsed time is more than 30 seconds.
And the explain plan:
cbq> explain SELECT a FROM couchmusic2 AS a WHERE a.countryCode = ‘FI’ ;
{
“requestID”: “39975487-a365-45b5-abe2-dca6f0a866e8”,
“signature”: “json”,
“results”: [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “PrimaryScan”,
“index”: “ndx_primary”,
“keyspace”: “couchmusic2”,
“namespace”: “default”,
“using”: “gsi”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Fetch”,
“as”: “a”,
“keyspace”: “couchmusic2”,
“namespace”: “default”
},
{
"#operator": “Filter”,
“condition”: “((a.countryCode) = “FI”)”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“expr”: “a”
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “1.570649ms”,
“executionTime”: “1.513094ms”,
“resultCount”: 1,
“resultSize”: 1517
}
}

cbq>

I’m a Couchbase dummy, because I come from Oracle database world.
What is indexed and how when I execute the :
CREATE PRIMARY INDEX NAME ON BUCKET;
Any doc or paper to understand couchbase indexing?

Many Thanks
Arturo

6

Try the following index:

CREATE INDEX idx_country ON couchmusic2(countryCode);

This is the same thing you would do in Oracle.

7

Hello Gerald,
Thanks for the help.
I’ve tried create the index in countrycode with this error;

cbq> CREATE INDEX idx_country ON couchmusic2(countryCode);
{
“requestID”: “09431e82-30ed-4486-80a7-dce2272ed223”,
“signature”: null,
“results”: [
],
“errors”: [
{
“code”: 12005,
“msg”: “Indexer not implemented GSI may not be enabled”
}
],
“status”: “errors”,
“metrics”: {
“elapsedTime”: “27.381881ms”,
“executionTime”: “27.320567ms”,
“resultCount”: 0,
“resultSize”: 0,
“errorCount”: 1
}
}

How fix it?
Thanks
Arturo

8

Try restarting all your Couchbase nodes.