Hello,
N1QL seems slow, so i compared the same behavior on MySQL.
1 000 000 rows like that :
uri partOfTown partOfNafClassification
===========================================================
15878ab8fa733a ville-90 eco/NafClassification/1509C
15878ab8fa7e43 ville-1 eco/NafClassification/1635C
15878ab8fa84ae ville-98 eco/NafClassification/1959C
Query :
select uri, partOfNafClassification, partOfTown FROM data WHERE partOfTown = 'ville-33'
and partOfNafClassification = 'eco/NafClassification/1000C' limit 1
Result in 3ms.
On CouchBase :
1 000 000 rows like that :
{
"uri": "entreprise-158788affabad4",
"partOfTown": "ville-42",
"partOfNafClassification": "eco/NafClassification/1073C"
}
Query :
select uri, partOfNafClassification, partOfTown FROM benchmark USE INDEX (idxmulti) WHERE partOfTown = 'ville-33'
and partOfNafClassification = 'eco/NafClassification/1000C'
limit 1
Result in 100ms.
Explain of Query :
[
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan",
"index": "idxmulti",
"index_id": "fdd3118d11ad0b9b",
"keyspace": "benchmark",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"\"eco/NafClassification/1000C\"",
"\"eco/NafClassification/1000C\""
],
"Inclusion": 3,
"Low": [
"\"eco/NafClassification/1000C\"",
"\"eco/NafClassification/1000C\""
]
}
}
],
"using": "gsi"
},
{
"#operator": "Fetch",
"keyspace": "benchmark",
"namespace": "default"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "(((`benchmark`.`partOfTown`) = \"ville-33\") and ((`benchmark`.`partOfNafClassification`) = \"eco/NafClassification/1000C\"))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "(`benchmark`.`uri`)"
},
{
"expr": "(`benchmark`.`partOfNafClassification`)"
},
{
"expr": "(`benchmark`.`partOfTown`)"
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
},
{
"#operator": "Limit",
"expr": "1"
}
]
},
"text": "select uri, partOfNafClassification, partOfTown FROM benchmark USE INDEX (idxmulti) WHERE partOfTown = 'ville-33'\nand partOfNafClassification = 'eco/NafClassification/1000C'\nlimit 1"
}
]