Can't use cover index with an index

SQL++
1

Hello,

I have the following indexes on 4.5.1 enterprise.

CREATE INDEX `idx_creative_username` ON `catalog`(`username`) WHERE (`form` = "creative")

CREATE INDEX `idx_creative_form` ON `catalog`(`form`) WHERE (`form` = "creative")

CREATE INDEX `idx_creative_deleted_at` ON `catalog`((`values`.`deleted_at`)) WHERE (`form` = "creative")

I’m trying to use cover indexes with the following query. Using “use index” does not work as well.

 explain select meta('bucket').id from bucket where form = 'creative' AND username ='test' AND (`values`.deleted_at is null or `values`.deleted_at is missing);

explain result:

    "results": [
        {
            "plan": {
                "#operator": "Sequence",
                "~children": [
                    {
                        "#operator": "IntersectScan",
                        "scans": [
                            {
                                "#operator": "IndexScan",
                                "index": "idx_creative_username",
                                "index_id": "f11359d524d4da45",
                                "keyspace": "bucket",
                                "namespace": "default",
                                "spans": [
                                    {
                                        "Range": {
                                            "High": [
                                                "\"test\""
                                            ],
                                            "Inclusion": 3,
                                            "Low": [
                                                "\"test\""
                                            ]
                                        }
                                    }
                                ],
                                "using": "gsi"
                            },
                            {
                                "#operator": "IndexScan",
                                "index": "idx_creative_form",
                                "index_id": "1ab5f2d0ba2195a9",
                                "keyspace": "bucket",
                                "namespace": "default",
                                "spans": [
                                    {
                                        "Range": {
                                            "High": [
                                                "\"creative\""
                                            ],
                                            "Inclusion": 3,
                                            "Low": [
                                                "\"creative\""
                                            ]
                                        }
                                    }
                                ],
                                "using": "gsi"
                            }
                        ]
                    },
                    {
                        "#operator": "Parallel",
                        "~child": {
                            "#operator": "Sequence",
                            "~children": [
                                {
                                    "#operator": "Fetch",
                                    "keyspace": "bucket",
                                    "namespace": "default"
                                },
                                {
                                    "#operator": "Filter",
                                    "condition": "((((`bucket`.`form`) = \"creative\") and ((`bucket`.`username`) = \"test\")) and ((((`bucket`.`values`).`deleted_at`) is null) or (((`bucket`.`values`).`deleted_at`) is missing)))"
                                },
                                {
                                    "#operator": "InitialProject",
                                    "result_terms": [
                                        {
                                            "expr": "(meta(\"bucket\").`id`)"
                                        }
                                    ]
                                },
                                {
                                    "#operator": "FinalProject"
                                }
                            ]
                        }
                    }
                ]
            },
            "text": "select meta('bucket').id from bucket where form = 'creative' AND username ='test' AND (`values`.deleted_at is null or `values`.deleted_at is missing)"
        }
    ],

When I take out values.deleted_at values, then it takes cover indexes.

This query works:

 explain select meta('bucket').id from bucket where form = 'creative' AND username ='test'

Am I doing something wrong?

2

A covering index means a single index has all the data needed to answer your query. For example:

CREATE INDEXidx_creative_usernameONcatalog(username) WHERE form = “creative” AND (values.deleted_at is null or values.deleted_at is missing );`

3

Thank you geraldss.

Ah…sorry about that geraldss. I forgot about it :slight_smile:

1 Like
4

It looks like I actually need to include values.deleted_at to use cover index

CREATE INDEXidx_creative_usernameONcatalog(username, values.deleted_at) WHERE form = “creative” AND (values.deleted_at is null or values.deleted_at is missing );`

1 Like