Run Your First Capella Queries

• Step 1 - Query Workbench
• Step 2 - First Query
• Step 3 - Build an Index

Query Workbench

On clusters running the Query Service, Couchbase Capella provides an interactive query tool called the Query Workbench. Using the Query Workbench, you can conveniently explore data, create, edit, run, and save query results. You can also explore the document structures in a bucket — all in a single window.

The Query Workbench is available under your cluster’s Tools > Query Workbench.

First Query

Couchbase makes it easy for teams to leverage their SQL knowledge. The power of SQL as a declarative language, combined with our cost-based optimizer, makes data access simple, even for JOINS across documents - which is complex in other document databases.

Let’s run our first query. The following query uses an inner join to list the source airports and airlines that fly into SFO, where only the non-null route documents join with matching airline documents. Copy and paste the following query into the Query Editor, then click [Execute]. Please note the execution time of the query. It will be displayed in milliseconds.

SELECT route.airlineid, airline.name, route.sourceairport, route.destinationairport
FROM `travel-sample` route
INNER JOIN `travel-sample` airline
ON route.airlineid = META(airline).id
WHERE route.type = "route"
AND route.destinationairport = "SFO"
ORDER BY route.sourceairport;

The results are displayed in JSON format in the Query Results field.

If you select the Plan button, Capella will show how the query was executed. We can look at the data-flow diagram to see query operators. Initial scans at the right, final output on the left. Potentially expensive operators are highlighted. Fetch represents almost 90% of the time spent.

To learn more about using the Query Workbench, refer to the Query Workbench page.

Build an Index

Let’s speed this query up with our Indexing Service. To determine the right index, we’ll press the Advise button. This gives us a lot of information and recommends an index. You can either copy the recommended CREATE INDEX command from the results or use the statement below. Select the Select statement, paste the CREATE INDEX statement, and press Execute. This will take a few seconds to run.

CREATE INDEX adv_destinationairport_sourceairport_airlineid_type ON `travel-sample`(`destinationairport`,`sourceairport`,`airlineid`) WHERE `type` = 'route'

Now let’s re-run the Select statement.

1. Click the [History] button.

This will show you all previous queries.

2. Click the SELECT statement.

In doing so, Capella will automatically populate the Query Editor with this statement.

3. Click Execute to re-run the statement.

Now take a look at the execution time. It is radically lower, and should be around 10 milliseconds.

Congratulations. You finished this part of the tutorial. Click "Next Step" to move to the next section.