Querying in N1ql taking too long

SQL++
1

Hey,

I have stored json documents of following structure in my bucket named “pincodeBucket”.

Structure ( or fields ) in json
{
“pincode”: “110001”,
“id”: 4,
“cityId”: 125,
“stateId”: 7,
“type”: “pincode”,
}

And I have done indexing like -
create primary index on pincode;
create index idx1 on pincodeBucket(pin code);
create index idx2 on pincodeBucket(type) where type = “pincode”;

And when I am querying pincodeBucket.query(Query.simple("select * from pincodeBucket where type =‘pincode’));
It is taking too long ( like 15-20 sec) to execute while in mysql same query takes about 4-5 sec.

I have about 22,000 documents in my bucket. All documents have type = “pincode”.

There must be a way to increase the speed, right ?
Am I doing something wrong while indexing ??
Please help

@daschl @simonbasle @ingenthr

2

Hi,

Try changing idx2 to: create index idx2 on pincodeBucket(type);

3

I have done that also. It still takes almost same amount of time. :frowning:

4

@akshay can you post the output of --> EXPLAIN select * from pincodeBucket where type =‘pincode’ ?

5

@daschl
Here is the Result of EXPLAIN select * from pincodeBucket where type =‘pincode’ ;

{
“results”: [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexScan”,
“index”: “idx2”,
“keyspace”: “PincodeBucket”,
“limit”: 9223372036854776000,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
“pincode”
],
“Inclusion”: 3,
“Low”: [
“pincode”
]
},
“Seek”: null
}
]
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Fetch”,
“keyspace”: “PincodeBucket”,
“namespace”: “default”
},
{
"#operator": “Filter”,
“condition”: “((PincodeBucket.type) = “pincode”)”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“star”: true
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
}
]
}

6

my index idx2 is
create index idx2 on pincodeBucket(type);

7

it seems you’re index has seriously low cardinality. in terms of MySQL it shouldn’t use index at all, it’s easier to scan table directly. If you’ll add a hint to your MySQL query to mandatory use index on ‘type’ field you’ll get same performance degradation.
My recommendation would be to drop this index at all.

P.S. ‘usually’ in RDBMS systems query optimizer turns on indexes when less than 10% of data will be selected.