<p>I've just heard the term covered index in some database discussion - what does it mean?</p>

[{'answer_id': 62140, 'author': 'Lasse V. Karlsen', 'author_id': 267, 'author_profile': 'https://Stackoverflow.com/users/267', 'pm_score': 7, 'selected': True, 'text': '<p>A <em>covering index</em> is an index that contains all of, and possibly more, the columns you need for your query.</p>\n\n<p>For instance, this:</p>\n\n<pre><code>SELECT *\nFROM tablename\nWHERE criteria\n</code></pre>\n\n<p>will typically use indexes to speed up the resolution of which rows to retrieve using <em>criteria</em>, but then it will go to the full table to retrieve the rows.</p>\n\n<p>However, if the index contained the columns <em>column1, column2</em> and <em>column3</em>, then this sql:</p>\n\n<pre><code>SELECT column1, column2\nFROM tablename\nWHERE criteria\n</code></pre>\n\n<p>and, provided that particular index could be used to speed up the resolution of which rows to retrieve, the index already contains the values of the columns you\'re interested in, so it won\'t have to go to the table to retrieve the rows, but can produce the results directly from the index.</p>\n\n<p>This can also be used if you see that a typical query uses 1-2 columns to resolve which rows, and then typically adds another 1-2 columns, it could be beneficial to append those extra columns (if they\'re the same all over) to the index, so that the query processor can get everything from the index itself.</p>\n\n<p>Here\'s an <a href="http://www.devx.com/dbzone/Article/29530" rel="noreferrer">article: Index Covering Boosts SQL Server Query Performance</a> on the subject.</p>\n'}, {'answer_id': 62143, 'author': 'aku', 'author_id': 1196, 'author_profile': 'https://Stackoverflow.com/users/1196', 'pm_score': 4, 'selected': False, 'text': '<p>Covering index is just an ordinary index. It\'s called "covering" if it can satisfy query without necessity to analyze data.</p>\n\n<p>example:</p>\n\n<pre><code>CREATE TABLE MyTable\n(\n  ID INT IDENTITY PRIMARY KEY, \n  Foo INT\n) \n\nCREATE NONCLUSTERED INDEX index1 ON MyTable(ID, Foo)\n\nSELECT ID, Foo FROM MyTable -- All requested data are covered by index\n</code></pre>\n\n<p>This is one of the fastest methods to retrieve data from SQL server.</p>\n'}, {'answer_id': 37800438, 'author': 'Thomas W', 'author_id': 768795, 'author_profile': 'https://Stackoverflow.com/users/768795', 'pm_score': 2, 'selected': False, 'text': '<p>Covering indexes are indexes which "cover" all columns needed from a specific table, removing the need to access the physical table at all for a given query/ operation.</p>\n\n<p>Since the index contains the desired columns (or a superset of them), table access can be replaced with an index lookup or scan -- which is generally much faster.</p>\n\n<p>Columns to cover:</p>\n\n<ul>\n<li>parameterized or static conditions;  columns restricted by a parameterized or constant condition.</li>\n<li>join columns;  columns dynamically used for joining</li>\n<li>selected columns;  to answer selected values.</li>\n</ul>\n\n<p>While covering indexes can often provide good benefit for retrieval, they do add somewhat to insert/ update overhead;  due to the need to write extra or larger index rows on every update. </p>\n\n<h2>Covering indexes for Joined Queries</h2>\n\n<p>Covering indexes are probably most valuable as a performance technique for joined queries. This is because joined queries are more costly &amp; more likely then single-table retrievals to suffer high cost performance problems. </p>\n\n<ul>\n<li>in a joined query, covering indexes should be considered per-table.</li>\n<li>each \'covering index\' removes a physical table access from the plan &amp; replaces it with index-only access.</li>\n<li>investigate the plan costs &amp; experiment with which tables are most worthwhile to replace by a covering index. </li>\n<li>by this means, the multiplicative cost of large join plans can be significantly reduced. </li>\n</ul>\n\n<p>For example:</p>\n\n<pre><code>select oi.title, c.name, c.address\nfrom porderitem poi\njoin porder po on po.id = poi.fk_order\njoin customer c on c.id = po.fk_customer\nwhere po.orderdate &gt; ? and po.status = \'SHIPPING\';\n\ncreate index porder_custitem on porder (orderdate, id, status, fk_customer);\n</code></pre>\n\n<p>See:</p>\n\n<ul>\n<li><a href="http://literatejava.com/sql/covering-indexes-query-optimization/" rel="nofollow">http://literatejava.com/sql/covering-indexes-query-optimization/</a></li>\n</ul>\n'}, {'answer_id': 40494784, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 2, 'selected': False, 'text': '<p>Lets say you have a simple table with the below columns, you have only indexed Id here:</p>\n\n<pre><code>Id (Int), Telephone_Number (Int), Name (VARCHAR), Address (VARCHAR)\n</code></pre>\n\n<p>Imagine you have to run the below query and check whether its using index, and whether performing efficiently without I/O calls or not. Remember, you have only created an index on <code>Id</code>.</p>\n\n<pre><code>SELECT Id FROM mytable WHERE Telephone_Number = \'55442233\';\n</code></pre>\n\n<p>When you check for performance on this query you will be dissappointed, since <code>Telephone_Number</code> is not indexed this needs to fetch rows from table using I/O calls. So, this is not a covering indexed since there is some column in query which is not indexed, which leads to frequent I/O calls.</p>\n\n<p>To make it a covered index you need to create a composite index on <code>(Id, Telephone_Number)</code>.  </p>\n\n<p>For more details, please refer to this blog:\n<a href="https://www.percona.com/blog/2006/11/23/covering-index-and-prefix-indexes/" rel="nofollow noreferrer">https://www.percona.com/blog/2006/11/23/covering-index-and-prefix-indexes/</a></p>\n'}]
['https://Stackoverflow.com/questions/62137', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/5466/']