Posted on

Couchbase N1QL: To Query or To Analyze? Part 2

When you need to query documents using SQL, there are two options available in Couchbase. The Query service and the Analytics service. Our blog, N1QL: To Query or To Analyze? provides a detailed overview of both services. I highly recommend reading it before this one. This article aims to expand on the previous blog by adding some concrete, hands-on examples. For each example we’ll cover how to write the query in both services and we’ll look at the performance differences. The goal is that readers will walk away with more knowledge to help quickly identify patterns and use cases that best fit each service.

Summary

Before jumping into examples. Let’s refresh ourselves on the high-level key characteristics of the two services.

Posted on

Index Advisor for Couchbase N1QL Query Statement

Couchbase N1QL query statement

Overview

Index advisor is introduced in Couchbase server 6.5 as a developer preview feature. It targets at providing secondary index recommendation to help DBAs and developers optimize Couchbase N1QL query performance. This version is rule-based, and the index candidates will be generated following the design rules specified here.

  1. Leading array index key for unnest
  2. Equality predicates
  3. IN predicates
  4. Not less than/between/not greater than predicates
  5. Less than/greater than predicates
  6. Array predicates
  7. Derived join filter as leading key for left-hand-side keyspace
  8. IS NOT NULL/MISSING/VALUED predicates
  9. Functional predicates
  10. Partial index condition

Index advisor is designed to work in two ways:

Posted on

Get a Bigger Picture With Couchbase N1QL Window Functions and CTE

See Couchbase N1QL window functions more clearly.

Whether it is a personal need to understand your spending pattern from looking at the bank transactions, or seeking to improve the sales performance in your organization by looking at the sales activities, the real insights are only visible with additional aggregation and summarization of the transactional level data.

Traditional RDBMSs provide this capability through the expressive power of SQL aggregation. Window functions were added to the ANSI SQL standard to further extend the ability to express more complex questions with the SQL construct.