{"id":7940,"date":"2019-12-26T10:54:55","date_gmt":"2019-12-26T18:54:55","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/?p=7940"},"modified":"2025-06-13T17:21:52","modified_gmt":"2025-06-14T00:21:52","slug":"index-advisor-service","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/index-advisor-service\/","title":{"rendered":"Index Advisor Service for Couchbase N1QL(SQL for JSON)"},"content":{"rendered":"

We are excited to announce the release of :<\/span><\/p>\n

Couchbase Index Advisor Service<\/span><\/h3>\n

https:\/\/index-advisor.couchbase.com<\/a><\/h3>\n

Yet Another service? Why?<\/h4>\n

We released N1QL(SQL for JSON) a few years ago- so that you could use SQL to retrieve and manipulate JSON data. But then unless good indexes are created- the queries don’t perform as well! How would you know if the index you created is a good enough index for our query engine? So, we decided to put out all the rules that make a good index. But that involved too much reading! So we are going one step further to empower you. We are now releasing a service –Index Advisor<\/strong>-that accepts a query and gives out an index recommendation that would meet the expectations of our query engine-all without downloading the latest Couchbase server.<\/p>\n

For Who?<\/h4>\n

This service will provide index recommendations to help DBAs, developers, and architects optimize query performance and meet the SLAs.<\/p>\n

Index Advisor is released as part of Couchbase Server 6.5. If you have downloaded that version, then you have everything (and more) than what this service provides.<\/p>\n

When should you use this service?<\/h4>\n

If you:<\/p>\n

    \n
  1. Want to avoid reading the index creation<\/a> rules<\/a>, understand them, and implement them to find the appropriate indexes for your query\/queries\/workload.<\/li>\n
  2. \u00a0Do not want to download the latest Couchbase 6.5 server yet.<\/li>\n
  3. \u00a0Are using an older Couchbase version<\/strong> and need help creating the right indexes for your queries.<\/li>\n
  4. Want to generate advice for indexes without creating a bucket<\/strong> or uploading the schema or data.<\/li>\n<\/ol>\n

    Background:<\/h4>\n

    N1QL is the SQL\u00a0 for JSON data and metadata. Every query written in N1QL has a query plan prepared by the N1QL query engine. The performance and efficiency of a query depend on its plan. Creation of right indexes for the data helps in selection of those indexes which can retrieve the result set in the most efficient manner. Even though JSON document itself has no schema, the index needs to have a schema.<\/p>\n

    For e.g index ix1(field1,field2)<\/span> is different from index ix2(field2,field1)<\/span>.<\/p>\n

    So not only the fields we select for an index are important, the order in which they are present in an index is also important for GSI indexes. (FTS index -we shall cover in another blog.)<\/p>\n

    Life of a Query:<\/h4>\n

    \"Screen<\/p>\n

     <\/p>\n

    Depending on how good step 4 is- we can minimize\/completely skip steps 5 and 6 and thereby improve the performance of the query.So deciding fields in an index is a very important part. To help with this we are releasing Index Advisor.<\/p>\n

    https:\/\/index-advisor.couchbase.com<\/a><\/p>\n

    Index Advisor – the service!<\/h4>\n

    With Index Advisor, you can<\/p>\n

      \n
    1. Provide a query or multiple queries from even a server older than 6.5<\/strong> and the service will recommend the indexes you should have\/create to get the best performance.<\/li>\n
    2. Since we do not have your data or schema, we are not able to see if you currently have those indexes. We will soon be improving this interface to accept data\/statistics\/schema\/infer op\/current indexes – or whatever else you can provide for us to make better recommendations.<\/li>\n<\/ol>\n

      Once you give us the query with ADVISE directive, the tool will give the recommendations for minimal index and covering index.<\/p>\n