{"id":2048,"date":"2015-12-21T09:05:00","date_gmt":"2015-12-21T09:05:00","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/?p=2048"},"modified":"2015-12-21T09:05:00","modified_gmt":"2015-12-21T09:05:00","slug":"introduction-to-covering-indexes","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/introduction-to-covering-indexes\/","title":{"rendered":"Introduction to covering indexes"},"content":{"rendered":"<h2>What are covering indexes?<\/h2>\n<p>In the relational\/SQL world you\u00a0can save query time by the judicious use of indexes: if you have an index on each column that your query touches then your DBMS can return the results without diving into the tables themselves.<\/p>\n<p>These are covering indexes.<\/p>\n<h2>What are covering indexes in Couchbase?<\/h2>\n<p>Couchbase Server 4.1 brings covering indexes to N1QL. If you have an index\u00a0that covers all of the data required by your N1QL query, Couchbase Server can return the data straight from that index. This cuts at least one, and possibly several, document reads from the query process.<\/p>\n<h2 style=\"margin-top: 0.7em; color: rgb(51, 51, 51); text-align: left;\">How does it work?<\/h2>\n<p>When you submit a N1QL query to Couchbase, it goes through the following steps:<\/p>\n<ol>\n<li>Parses your query, analyses what it does and then creates a query plan.<\/li>\n<li>Checks the relevant indexes.<\/li>\n<li>Gets the keys for the documents that contain the results and then fetches the documents themselves.<\/li>\n<li>Pulls the relevant data out of those documents to create the result.<\/li>\n<li>Returns the result to your application.<\/li>\n<\/ol>\n<p>With a covering index, all the data for the result is in the index itself so you cut out steps 3 and 4.<\/p>\n<h2>How do I use covering indexes?<\/h2>\n<p>Nothing changes about how you write your queries. Instead, this is all about how you set-up the indexes. A single index &#8212; GSI or view &#8212; must cover every JSON key that your query touches.<\/p>\n<h2 style=\"margin-top: 0.7em; color: rgb(51, 51, 51); text-align: left;\">Got an example?<\/h2>\n<p>Let&apos;s say we&apos;re storing customer profiles. They might look like this:<\/p>\n<p>{<\/p>\n<p style=\"margin-left: 40px;\">&#8220;name&#8221;: &#8220;Sherlock Holmes&#8221;,<\/p>\n<p style=\"margin-left: 40px;\">&#8220;address&#8221;:\u00a0&#8220;221b Baker Street&#8221;,<\/p>\n<p style=\"margin-left: 40px;\">&#8220;city&#8221;: &#8220;London&#8221;,<\/p>\n<p style=\"margin-left: 40px;\">&#8220;country&#8221;: &#8220;UK&#8221;,<\/p>\n<p style=\"margin-left: 40px;\">&#8220;newsletter&#8221;: true,<\/p>\n<p style=\"margin-left: 40px;\">&#8220;joinedDate&#8221;:\u00a01168041600<\/p>\n<p>}<\/p>\n<p>If we wanted to find the name and city of everyone who is subscribed to our newsletter, we could write a N1QL query like this:<\/p>\n<pre>\nSELECT name, city FROM `customers` WHERE newsletter=true;<\/pre>\n<p>Without a covering index, this query would pull <em>name<\/em> and <em>city\u00a0<\/em>from our customer records by doing a\u00a0look-up of those documents.<\/p>\n<p>We can create a covering index for this query like this:<\/p>\n<pre>\nCREATE INDEX newsletter_subs ON `customers`(name, city, newsletter) USING GSI;<\/pre>\n<p>Now the query can be satisifed entirely from the index.<\/p>\n<h2>Where can I read more?<\/h2>\n<p>There&apos;s a <a href=\"https:\/\/developer.couchbase.com\/documentation\/server\/4.1\/indexes\/covering-indexes.html\">section on covering indexes in the Couchbase Server documentation<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>What are covering indexes? In the relational\/SQL world you\u00a0can save query time by the judicious use of indexes: if you have an index on each column that your query touches then your DBMS can return the results without diving into [&hellip;]<\/p>\n","protected":false},"author":18,"featured_media":13873,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"inline_featured_image":false,"footnotes":""},"categories":[1],"tags":[],"ppma_author":[8982],"class_list":["post-2048","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v25.7.1 (Yoast SEO v25.7) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Introduction to covering indexes - The Couchbase Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.couchbase.com\/blog\/introduction-to-covering-indexes\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Introduction to covering indexes\" \/>\n<meta property=\"og:description\" content=\"What are covering indexes? In the relational\/SQL world you\u00a0can save query time by the judicious use of indexes: if you have an index on each column that your query touches then your DBMS can return the results without diving into [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.couchbase.com\/blog\/introduction-to-covering-indexes\/\" \/>\n<meta property=\"og:site_name\" content=\"The Couchbase Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-12-21T09:05:00+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2022\/11\/couchbase-nosql-dbaas.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1800\" \/>\n\t<meta property=\"og:image:height\" content=\"630\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Matthew Revell, Lead Developer Advocate, EMEA, Couchbase\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Matthew Revell, Lead Developer Advocate, EMEA, Couchbase\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/introduction-to-covering-indexes\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/introduction-to-covering-indexes\/\"},\"author\":{\"name\":\"Matthew Revell, Lead Developer Advocate, EMEA, Couchbase\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/6c3060a94353df62a71d4672b3454555\"},\"headline\":\"Introduction to covering indexes\",\"datePublished\":\"2015-12-21T09:05:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/introduction-to-covering-indexes\/\"},\"wordCount\":359,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/introduction-to-covering-indexes\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2022\/11\/couchbase-nosql-dbaas.png\",\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.couchbase.com\/blog\/introduction-to-covering-indexes\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/introduction-to-covering-indexes\/\",\"url\":\"https:\/\/www.couchbase.com\/blog\/introduction-to-covering-indexes\/\",\"name\":\"Introduction to covering indexes - The Couchbase Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/introduction-to-covering-indexes\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/introduction-to-covering-indexes\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2022\/11\/couchbase-nosql-dbaas.png\",\"datePublished\":\"2015-12-21T09:05:00+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/introduction-to-covering-indexes\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.couchbase.com\/blog\/introduction-to-covering-indexes\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/introduction-to-covering-indexes\/#primaryimage\",\"url\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2022\/11\/couchbase-nosql-dbaas.png\",\"contentUrl\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2022\/11\/couchbase-nosql-dbaas.png\",\"width\":1800,\"height\":630},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/introduction-to-covering-indexes\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.couchbase.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Introduction to covering indexes\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/#website\",\"url\":\"https:\/\/www.couchbase.com\/blog\/\",\"name\":\"The Couchbase Blog\",\"description\":\"Couchbase, the NoSQL Database\",\"publisher\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.couchbase.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/#organization\",\"name\":\"The Couchbase Blog\",\"url\":\"https:\/\/www.couchbase.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2023\/04\/admin-logo.png\",\"contentUrl\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2023\/04\/admin-logo.png\",\"width\":218,\"height\":34,\"caption\":\"The Couchbase Blog\"},\"image\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/6c3060a94353df62a71d4672b3454555\",\"name\":\"Matthew Revell, Lead Developer Advocate, EMEA, Couchbase\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/image\/b1bc555cd9166b46d6063003c3b92317\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/3b38ea45b78371f0008a765ea828bfed91aa97c25981ebf214226402a510b39b?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/3b38ea45b78371f0008a765ea828bfed91aa97c25981ebf214226402a510b39b?s=96&d=mm&r=g\",\"caption\":\"Matthew Revell, Lead Developer Advocate, EMEA, Couchbase\"},\"description\":\"Matthew Revell is a Lead Dev Advocate, EMEA Couchbase. He developed a global strategy for putting Couchbase front in the minds of the product's developers.\",\"url\":\"https:\/\/www.couchbase.com\/blog\/author\/matthew-revell\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Introduction to covering indexes - The Couchbase Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.couchbase.com\/blog\/introduction-to-covering-indexes\/","og_locale":"en_US","og_type":"article","og_title":"Introduction to covering indexes","og_description":"What are covering indexes? In the relational\/SQL world you\u00a0can save query time by the judicious use of indexes: if you have an index on each column that your query touches then your DBMS can return the results without diving into [&hellip;]","og_url":"https:\/\/www.couchbase.com\/blog\/introduction-to-covering-indexes\/","og_site_name":"The Couchbase Blog","article_published_time":"2015-12-21T09:05:00+00:00","og_image":[{"width":1800,"height":630,"url":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2022\/11\/couchbase-nosql-dbaas.png","type":"image\/png"}],"author":"Matthew Revell, Lead Developer Advocate, EMEA, Couchbase","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Matthew Revell, Lead Developer Advocate, EMEA, Couchbase","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.couchbase.com\/blog\/introduction-to-covering-indexes\/#article","isPartOf":{"@id":"https:\/\/www.couchbase.com\/blog\/introduction-to-covering-indexes\/"},"author":{"name":"Matthew Revell, Lead Developer Advocate, EMEA, Couchbase","@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/6c3060a94353df62a71d4672b3454555"},"headline":"Introduction to covering indexes","datePublished":"2015-12-21T09:05:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.couchbase.com\/blog\/introduction-to-covering-indexes\/"},"wordCount":359,"commentCount":0,"publisher":{"@id":"https:\/\/www.couchbase.com\/blog\/#organization"},"image":{"@id":"https:\/\/www.couchbase.com\/blog\/introduction-to-covering-indexes\/#primaryimage"},"thumbnailUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2022\/11\/couchbase-nosql-dbaas.png","inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.couchbase.com\/blog\/introduction-to-covering-indexes\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.couchbase.com\/blog\/introduction-to-covering-indexes\/","url":"https:\/\/www.couchbase.com\/blog\/introduction-to-covering-indexes\/","name":"Introduction to covering indexes - The Couchbase Blog","isPartOf":{"@id":"https:\/\/www.couchbase.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.couchbase.com\/blog\/introduction-to-covering-indexes\/#primaryimage"},"image":{"@id":"https:\/\/www.couchbase.com\/blog\/introduction-to-covering-indexes\/#primaryimage"},"thumbnailUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2022\/11\/couchbase-nosql-dbaas.png","datePublished":"2015-12-21T09:05:00+00:00","breadcrumb":{"@id":"https:\/\/www.couchbase.com\/blog\/introduction-to-covering-indexes\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.couchbase.com\/blog\/introduction-to-covering-indexes\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.couchbase.com\/blog\/introduction-to-covering-indexes\/#primaryimage","url":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2022\/11\/couchbase-nosql-dbaas.png","contentUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2022\/11\/couchbase-nosql-dbaas.png","width":1800,"height":630},{"@type":"BreadcrumbList","@id":"https:\/\/www.couchbase.com\/blog\/introduction-to-covering-indexes\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.couchbase.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Introduction to covering indexes"}]},{"@type":"WebSite","@id":"https:\/\/www.couchbase.com\/blog\/#website","url":"https:\/\/www.couchbase.com\/blog\/","name":"The Couchbase Blog","description":"Couchbase, the NoSQL Database","publisher":{"@id":"https:\/\/www.couchbase.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.couchbase.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.couchbase.com\/blog\/#organization","name":"The Couchbase Blog","url":"https:\/\/www.couchbase.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2023\/04\/admin-logo.png","contentUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2023\/04\/admin-logo.png","width":218,"height":34,"caption":"The Couchbase Blog"},"image":{"@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/6c3060a94353df62a71d4672b3454555","name":"Matthew Revell, Lead Developer Advocate, EMEA, Couchbase","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/image\/b1bc555cd9166b46d6063003c3b92317","url":"https:\/\/secure.gravatar.com\/avatar\/3b38ea45b78371f0008a765ea828bfed91aa97c25981ebf214226402a510b39b?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/3b38ea45b78371f0008a765ea828bfed91aa97c25981ebf214226402a510b39b?s=96&d=mm&r=g","caption":"Matthew Revell, Lead Developer Advocate, EMEA, Couchbase"},"description":"Matthew Revell is a Lead Dev Advocate, EMEA Couchbase. He developed a global strategy for putting Couchbase front in the minds of the product's developers.","url":"https:\/\/www.couchbase.com\/blog\/author\/matthew-revell\/"}]}},"authors":[{"term_id":8982,"user_id":18,"is_guest":0,"slug":"matthew-revell","display_name":"Matthew Revell, Lead Developer Advocate, EMEA, Couchbase","avatar_url":"https:\/\/secure.gravatar.com\/avatar\/3b38ea45b78371f0008a765ea828bfed91aa97c25981ebf214226402a510b39b?s=96&d=mm&r=g","author_category":"","last_name":"Revell","first_name":"Matthew","job_title":"","user_url":"","description":"Matthew Revell is a Lead Dev Advocate, EMEA Couchbase. He developed a global strategy for putting Couchbase front in the minds of the product's developers."}],"_links":{"self":[{"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/posts\/2048","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/users\/18"}],"replies":[{"embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/comments?post=2048"}],"version-history":[{"count":0,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/posts\/2048\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/media\/13873"}],"wp:attachment":[{"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/media?parent=2048"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/categories?post=2048"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/tags?post=2048"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/ppma_author?post=2048"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}