Covering index not working (or I did something wrong)

If I create a covering index:

Definition

CREATE INDEX `covering_idx` ON `EON`(`marketCountry`,`catagory`,`severity`,`issue`,`createDate`,`createTime`,`status`,`parentJira`,`childrenJira`,`title`,`updateCtr`,`notifyUser`,`subcatagory`,`updateDate`,`updateTime`)

should I not be able to do:

SELECT * FROM EON

I also tried

SELECT marketCountry,catagory,severity,issue,createDate,createTime,status,parentJira,childrenJira,title,updateCtr,notifyUser,subcatagory,updateDate,updateTime FROM EON

The explain returns:

"errors": [
    {
      "code": 4000,
      "msg": "No index available on keyspace EON that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.",
      "query_from_user": "explain SELECT marketCountry,catagory,severity,issue,createDate,createTime,status,parentJira,childrenJira,title,updateCtr,notifyUser,subcatagory,updateDate,updateTime FROM EON"
    }

Am I missing something?

Thanks

Yes, your query has no where clause so it can only use a primary index.
To use the covering index you need to query based on the index
SELECT marketCountry,catagory,severity… FROM EON where marketCountry != “”

SELECT * FROM EON where marketCountry != “” will also then work, but I suspect SELECT *, can’t be satisfied by the covered index alone and will still request the actual data records.

In short, without a primary index, you can only do queries that can use an existing index. And the data will be still loaded unless all the fields selected exist in the index.

Forgive me if I’ve gotten something wrong here, I’ve only been looking into using this for a few hours now.

1 Like

Check out https://blog.couchbase.com/n1ql-practical-guide-second-edition/

thank you, I should of seen/known that ………

again thanks