{{announcement.body}}
{{announcement.title}}

Index Advisor Service for Couchbase N1QL (SQL for JSON)

DZone 's Guide to

Index Advisor Service for Couchbase N1QL (SQL for JSON)

See how it will help provide index recommendations to help DBAs, developers, and architects optimize query performance and meet the SLAs.

· Database Zone ·
Free Resource

Couchbase N1QL is a SQL-like language for JSON data. To retrieve and manipulate JSON data effectively, we need appropriate indexes. The rules for creating these indexes can be read here. But that involves too much reading, hence we now have an Index Advisor service that accepts a query and gives out an index recommendation that would meet the expectations of the Couchbase query engine — all without downloading the latest Couchbase server.

This service will provide index recommendations to help DBAs, developers, and architects optimize query performance and meet the SLAs.

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. This service gets a monthly refresh. So far, after the initial release of the service in Jan, there have been refreshes in Feb and March for defects found by QE and customers using the service.

When Should You Use This Service

If you:

  1. Want to avoid reading the index creation rules, understanding them, or implementing them to find the appropriate indexes for your query/queries/workload.
  2. Do not want to download the latest Couchbase 6.5 server yet.
  3. Want to stick to Couchbase Community Edition
  4. Are using an older Couchbase version and need help creating the right indexes for your queries.
  5. Want to generate advice for indexes without creating a bucket or uploading the schema or data.
You might also like: N1QL and SEARCH: How to Leverage Couchbase Full-Text Index (FTS) in N1QL

Background

N1QL is the SQL 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. The creation of the right indexes for the data helps in the selection of those indexes that can retrieve the result set in the most efficient manner. Even though the JSON document itself has no schema, the index needs to have a schema.

For example, index ix1(field1,field2) is different from index ix2(field2,field1).

So not only are the fields we select for an index important, the order in which they are present in an index is also important for GSI indexes.

Life of a Query:


 

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 the Index Advisor.

Index Advisor: The Service

With Index Advisor, you can

  1. Provide a query or multiple queries from even a server older than 6.5 and the service will recommend the indexes you should have/create to get the best performance.
  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.

Once you give us the query with the ADVISE directive, the tool will give the recommendations for minimal index and covering index.

  • Indexes: This section lists the recommended indexes based on predicates in WHERE/ON clause, along with the corresponding recommendation rule that each index follows.
  • Covering Indexes: This section lists the covering indexes applicable to the input query i.e. an index that includes all fields referenced in the query to avoid the extra hop to data service.

Here is the look and feel of the tool:


Examples:

1.

Java
 




xxxxxxxxxx
1


 
1
ADVISE SELECT fname, age, age/7 AS age_dog_years FROM trial WHERE fname = 'Sara'



Java
 




xxxxxxxxxx
1
28


1
{
2
  "results": [
3
    {
4
      "#operator": "Advise",
5
      "advice": {
6
        "#operator": "IndexAdvice",
7
        "adviseinfo": {
8
          "recommended_indexes": {
9
            "covering_indexes": [
10
              {
11
                "index_statement": "CREATE INDEX adv_fname_age ON `trial`(`fname`,`age`)",
12
                "keyspace_alias": "trial"
13
              }
14
            ],
15
            "indexes": [
16
              {
17
                "index_statement": "CREATE INDEX adv_fname ON `trial`(`fname`)",
18
                "keyspace_alias": "trial",
19
                "recommending_rule": "Index keys follow order of predicate types: 2. equality/null/missing."
20
              }
21
            ]
22
          }
23
        }
24
      },
25
      "query": "SELECT fname, age, age/7 AS age_dog_years FROM trial WHERE fname = 'Sara'"
26
    }
27
  ]
28
}



2.

Java
 




xxxxxxxxxx
1


 
1
ADVISE SELECT (DISTINCT purchases.customerId)FROM purchases 
2
WHERE purchases.purchasedAt BETWEEN "2014-03-01" AND "2014-03-31"



JSON
 




xxxxxxxxxx
1
24


 
1
{
2
  "results": [
3
    {
4
      "#operator": "Advise",
5
      "advice": {
6
        "#operator": "IndexAdvice",
7
        "adviseinfo": [
8
          {
9
            "recommended_indexes": {
10
              "indexes": [
11
                {
12
                  "index_statement": "CREATE INDEX adv_purchasedAt ON `purchases`(`purchasedAt`)",
13
                  "keyspace_alias": "purchases",
14
                  "recommending_rule": "Index keys follow order of predicate types: 4. not less than/between/not greater than."
15
                }
16
              ]
17
            }
18
          }
19
        ]
20
      },
21
      "query": "SELECT (DISTINCT purchases.customerId) \nFROM purchases\nWHERE purchases.purchasedAt BETWEEN \"2014-03-01\" AND \"2014-03-31\""
22
    }
23
  ]
24
}



3.

Java
 




xxxxxxxxxx
1


 
1
ADVISE SELECT DISTINCT airline.name,airport.name AS airport,route.distance 
2
 
          
3
FROM `travel-sample` airport INNER JOIN `travel-sample` route ON airport.faa = route.sourceairport
4
 
          
5
AND route.type = "route" INNER JOIN `travel-sample` airline ON route.airline = airline.iata AND airline.type = "airline" 




JSON
 




xxxxxxxxxx
1
39


 
1
{
2
  "results": [
3
    {
4
      "#operator": "Advise",
5
      "advice": {
6
        "#operator": "IndexAdvice",
7
        "adviseinfo": [
8
          {
9
            "recommended_indexes": {
10
              "covering_indexes": [
11
                {
12
                  "index_statement": "CREATE INDEX adv_type_sourceairport_airline_distance ON `travel-sample`(`type`,`sourceairport`,`airline`,`distance`)",
13
                  "keyspace_alias": "travel-sample_route"
14
                },
15
                {
16
                  "index_statement": "CREATE INDEX adv_type_iata_name ON `travel-sample`(`type`,`iata`,`name`)",
17
                  "keyspace_alias": "travel-sample_airline"
18
                }
19
              ],
20
              "indexes": [
21
                {
22
                  "index_statement": "CREATE INDEX adv_type_sourceairport ON `travel-sample`(`type`,`sourceairport`)",
23
                  "keyspace_alias": "travel-sample_route",
24
                  "recommending_rule": "Index keys follow order of predicate types: 2. equality/null/missing, 10. non-static join predicate."
25
                },
26
                {
27
                  "index_statement": "CREATE INDEX adv_type_iata ON `travel-sample`(`type`,`iata`)",
28
                  "keyspace_alias": "travel-sample_airline",
29
                  "recommending_rule": "Index keys follow order of predicate types: 2. equality/null/missing, 10. non-static join predicate."
30
                }
31
              ]
32
            }
33
          }
34
        ]
35
      },
36
      "query": "SELECT DISTINCT airline.name,airport.name AS airport,route.distance FROM `travel-sample` airport INNER JOIN `travel-sample` route ON airport.faa = route.sourceairport AND route.type = \"route\"\nINNER JOIN `travel-sample` airline ON route.airline = airline.iata AND airline.type = \"airline\"\nWHERE airport.type = \"airport\" AND airport.city = \"San Jose\";"
37
    }
38
  ]
39
}




4.

Java
 




xxxxxxxxxx
1


 
1
SELECT ADVISOR(["SELECT * FROM `travel-sample` WHERE type = 'hotel' AND city = 'Paris'", 
2
 
          
3
"SELECT * FROM `travel-sample` h JOIN `travel-sample` a ON a.city = h.city WHERE h.type = 'hotel' AND a.type = 'airport'"])


 

Result
JSON
 




xxxxxxxxxx
1
23


 
1
{
2
  "results": [
3
    {
4
      "$1": {
5
        "recommended_indexes": [
6
          {
7
            "index""CREATE INDEX adv_type_city ON `travel-sample`(`type`,`city`)",
8
            "statements": [
9
              {
10
                "run_count"1,
11
                "statement""SELECT * FROM `travel-sample` WHERE type = 'hotel' AND city = 'Paris'"
12
              },
13
              {
14
                "run_count"1,
15
                "statement""SELECT * FROM `travel-sample` h JOIN `travel-sample` a ON a.city = h.city                                 WHERE h.type = 'hotel' AND a.type = 'airport'"
16
              }
17
            ]
18
          }
19
        ]
20
      }
21
    }
22
  ]
23
}




The index candidates are generated following the design rules specified here.

What Remains to Be Done

1. Improve UI for this tool. Provide index recommendations in text for easier cut and paste. It currently provides a JSON output.

2. Accept flavors so we can generate partial index recommendations.

3. Provide a way to let the user enter schema, existing indexes, infer output, and maybe even test data.

We would love to hear from you on how you liked the tool, any additional features you would like to see. Please share your feedback via the comments.

Summary

The index advisor (ADVISE statement) provides index recommendations for a single query. It advises regular index,  array index, and covering index and provides information on the corresponding recommendation rule that each index key follows.

The index advisor (ADVISOR statement) provides index recommendations for multiple queries. It recommends as few indexes as possible that would be suitable for all the queries in the array.

Further Reading

Couchbase N1QL: To Query or To Analyze?

Index Advisor Service for Couchbase N1QL - Feb refresh

Index Advisor Service for Couchbase N1QL - March refresh

Index Advisor Service for Couchbase N1QL - June refresh

Using N1QL With Couchbase Eventing Functions

Topics:
index advisor, n1ql query, query optimization, querying data, secondary index, tutorial

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}