We have been exploring Couchbase N1QL as an alternative to MySQL and MongoDB and have found that the query performance is inconsistent and below par even on simple single-field queries. The outbound bucket used in the example below contains 3.5 million documents
cbq> select * from outbound where agent_id = 9001 and group_id = 7050 limit 1;
{
“requestID”: “b628e8a6-1f45-4238-ac26-47aa498457bb”,
“signature”: {
"": ""
},
“results”: [
{
“outbound”: {
“agent_id”: 9001,
“c3_id”: 1,
“called_number”: “16468107012”,
“duration”: 69,
“group_id”: 7050,
“hangup_cause”: null,
“recording_filename”: “3218_9001_16468107012.mp3”,
“session_id”: “eJDzxQbXR”,
“status”: “Answered”,
“time”: 1.399950081e+09,
“uuid”: “7.154027998590868e15”
}
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “130.241522ms”,
“executionTime”: “130.092024ms”,
“resultCount”: 1,
“resultSize”: 501
}
}
The fields queried upon: agent_id and group_id are both indexed as can be seen by the output of explain.
cbq> explain select * from outbound where agent_id = 9001 and group_id = 7050 }
{
“requestID”: “3b690b35-9ab5-4f9f-a156-6c07ca7c3a0b”,
“signature”: “json”,
“results”: [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexScan”,
“index”: “group_id”,
“keyspace”: “outbound”,
“limit”: 9.223372036854776e+18,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
“7050.000001”
],
“Inclusion”: 1,
“Low”: [
“7050”
]
},
“Seek”: null
}
],
“using”: “view”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Fetch”,
“keyspace”: “outbound”,
“namespace”: “default”
},
{
"#operator": “Filter”,
“condition”: “(((outbound.agent_id) = 9001) and ((outbound.group_id) = 7050))”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“star”: true
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
{
"#operator": “Limit”,
“expr”: “1”
}
]
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “35.060232ms”,
“executionTime”: “34.88145ms”,
“resultCount”: 1,
“resultSize”: 2715
}
}
Limiting the query to a single field as in “SELECT * FROM outbound WHERE agent_id = 9001 LIMIT 1;” takes around the same amount of time. Is there any further optimizations that can be applied to make the response better and consistent??