Query Data with Query Workbench


      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] menu.


      Running your 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.

      Speed up your Query: 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 Index creation 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. Press the History button. This will show you all previous queries. Click the Select statement. In doing so, Capella will automatically rerun the query on your behalf.

      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.