More than 11 seconds to execute an N1QL query

SQL++
,
1

Hi, I’m quite new to Couchbase and N1QL, so problably I’m missing something.

I want to query 3878 “city” documents, which are built in this way:
{
“authors”: [
{
“af_id”: “600”,
“af_name”: “sth”,
“auth_id”: “55756254000”,
“prof_ids”: [
“56962714100”
]
},
{
… (see above)
}
],
“cityname”: “Lusaka”,
“countryname”: “Zambia”,
“lat”: “-15.3875259”,
“lon”: “28.3228165”
}

and it’s succesfully queried with the following statement:

SELECT cityname, lat, lon
FROM unibo_international AS cities
WHERE countryname = 'Zambia’
AND ANY city IN cities.authors SATISFIES city.auth_id IN [‘12808118500’,‘13408790100’,‘22735645000’,
‘23390841100’,‘36158903400’,‘36907107900’,‘55396053900’,‘55798828100’] END

Countryname and ids array are passed as parameters.
The execution of this query, takes up to 12 seconds!

Ho do you think I can improve the execution of this query?
Thanks.

2

try to create index as following

CREATE INDEX `idx_countryname_auth_id_cityname_lat_lon` ON `unibo_international `(countryname,(distinct (array (`city`.`auth_id`) for `city` in `authors` end)),`cityname`,`lat`,`lon`)  USING GSI;

and then you will use coverIndex as following

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "DistinctScan",
        "scan": {
          "#operator": "IndexScan2",
          "covers": [
            "cover ((`cities`.`countryname`))",
            "cover ((distinct (array (`author`.`auth_id`) for `author` in (`cities`.`authors`) end)))",
            "cover ((`cities`.`cityname`))",
            "cover ((`cities`.`lat`))",
            "cover ((`cities`.`lon`))",
            "cover ((meta(`cities`).`id`))"
          ],
          "filter_covers": {
            "cover (any `city` in (`cities`.`authors`) satisfies ((`city`.`auth_id`) in [\"12808118500\", \"13408790100\", \"22735645000\", \"23390841100\", \"36158903400\", \"36907107900\", \"55396053900\", \"55798828100\"]) end)": true
          },
          "index": "idx_countryname_auth_id_cityname_lat_lon",
..................

to improve the execution of this query.

1 Like
3

Oh my god, thanks a lot! Works like a charm!

4

@cecca_92: see the articles on index design and query optimization in the book: x.com

2 Likes