Why executionTime is high?

Couchbase Server
1

Hello
I install Couchbase and entered travel-bucket data.
when test the sample query execution Time is 3.5 s

Is not there a lot for 32,000 records?

cbq> SELECT * FROM `travel-sample` WHERE name = "bmibaby";
{
    "requestID": "a10d1aa0-d6e5-42fb-a0c5-73922f1ca53d",
    "signature": {
        "*": "*"
    },
    "results": [
        {
            "travel-sample": {
                "callsign": "BABY",
                "country": "United Kingdom",
                "iata": "WW",
                "icao": "BMI",
                "id": 1441,
                "name": "bmibaby",
                "type": "airline"
            }
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "3.5293548s",
        "executionTime": "3.5293548s",
        "resultCount": 1,
        "resultSize": 295
    }
}
2

Can you post an EXPLAIN? Also did you create any other indexes besides the default ones on the travel sample.

3

Thank you.

cbq> EXPLAIN SELECT * FROM `travel-sample` WHERE name = "bmibaby";
{
    "requestID": "95eef0fc-a9ba-4657-8ceb-bede989af2d3",
    "signature": "json",
    "results": [
        {
            "plan": {
                "#operator": "Sequence",
                "~children": [
                    {
                        "#operator": "PrimaryScan",
                        "index": "def_primary",
                        "keyspace": "travel-sample",
                        "namespace": "default",
                        "using": "gsi"
                    },
                    {
                        "#operator": "Fetch",
                        "keyspace": "travel-sample",
                        "namespace": "default"
                    },
                    {
                        "#operator": "Parallel",
                        "~child": {
                            "#operator": "Sequence",
                            "~children": [
                                {
                                    "#operator": "Filter",
                                    "condition": "((`travel-sample`.`name`) = \"bmibaby\")"
                                },
                                {
                                    "#operator": "InitialProject",
                                    "result_terms": [
                                        {
                                            "expr": "self",
                                            "star": true
                                        }
                                    ]
                                },
                                {
                                    "#operator": "FinalProject"
                                }
                            ]
                        }
                    }
                ]
            },
            "text": "SELECT * FROM `travel-sample` WHERE name = \"bmibaby\";"
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "2.9989ms",
        "executionTime": "2.9989ms",
        "resultCount": 1,
        "resultSize": 1754
    }
}
4

CREATE INDEX tname ON travel-sample(name);

Check this article https://dzone.com/articles/designing-index-for-query-in-couchbase-n1ql

5

I made it faster when I indexed it.

for example:

when name field is indexed:
SELECT * FROM travel-sample WHERE name = “bmibaby”;
“executionTime”: “3.9999ms”

SELECT * FROM travel-sample WHERE name LIKE “%baby%”;
“executionTime”: “565.9962ms”,

when name field is not indexed:
SELECT * FROM travel-sample WHERE name = “bmibaby”;
“executionTime”: “3.1873891s”

But the same query in mysql is on 32000 records

when email field is indexed: (in mysql)
SELECT * FROM user WHERE email LIKE ‘%Aliquamadipiscinglobortis%’
6 total, Query took 0.0511 seconds

when email field is not indexed:(in mysql)
SELECT * FROM user WHERE email LIKE ‘%Aliquamadipiscinglobortis%’
6 total, Query took 0.2611 seconds

Why is there such a difference in the non-indexed state?
Why does speed decrease so much when using ‘LIKE’ in N1QL?

6

Please check this out http://blog.couchbase.com/wp-content/uploads/2017/03/N1QL-A-Practical-Guide-v2.pdf