What is a closed index?

Question

I just heard the term covered index in some kind of database discussion - what does that mean?

Answer 1

A coverage index is an index that contains everything and possibly more columns that you need for your query.

For example, this:

SELECT * FROM tablename WHERE criteria 

Usually uses indexes to speed up the decision about which rows to retrieve using the criteria, but then it will go to the full table to retrieve the rows.

However, if the index contains columns column1, column2 and column3, then this sql:

 SELECT column1, column2 FROM tablename WHERE criteria 

and provided that a specific index can be used to speed up the resolution of the rows that will be retrieved, the index already contains the values ​​of the columns of interest to you, so it will not need to go to the table to retrieve rows, but can output the results directly from the index.

This can also be used if you see that a typical query uses 1-2 columns to resolve these rows and then usually adds 1-2 more columns, it would be useful to add these additional columns (if they are the same as and all) to the index so that the query processor can get everything from the index itself.

Here's an article: Coverage of indexes improves SQL Server query performance on this.

Answer 2

A coverage index is just a regular index. It is called “coverage” if it can satisfy a request without the need for data analysis.

Example:

 CREATE TABLE MyTable ( ID INT IDENTITY PRIMARY KEY, Foo INT ) CREATE NONCLUSTERED INDEX index1 ON MyTable(ID, Foo) SELECT ID, Foo FROM MyTable -- All requested data are covered by index 

This is one of the fastest ways to retrieve data from an SQL server.

Answer 3

Coverage indices are indices that “cover” all the columns needed from a particular table, eliminating the need to access the physical table in general for a given query / operation.

Since the index contains the required columns (or a superset of them), access to the table can be replaced by index search or scanning, which is usually much faster.

Columns for coating:

• parameterized or static conditions; columns bounded by a parameterized or constant condition.
• join columns columns dynamically used for joining
• selected columns to answer the selected values.

Although index coverage can often provide good benefits for retrieval, they add a little to the attachment / updating of overhead; due to the need to write extra or large index lines with each update.

Index Coverage for Related Queries

Coverage indexes are probably the most valuable as a performance method for attached queries. This is because concatenated queries are more expensive and more likely than fetching from a single table in order to suffer from high cost problems.

• in a combined query, covering indexes should be taken into account in the table.
• each “coverage index” removes physical access to the table from the plan and replaces it with access only by index.
• Examine the costs of the plan and experiment with which tables are most appropriate to replace with a coverage index.
• Thus, the multiplicative value of large association plans can be significantly reduced.

For example:

 select oi.title, c.name, c.address from porderitem poi join porder po on po.id = poi.fk_order join customer c on c.id = po.fk_customer where po.orderdate > ? and po.status = 'SHIPPING'; create index porder_custitem on porder (orderdate, id, status, fk_customer); 

Cm:

• http://literatejava.com/sql/covering-indexes-query-optimization/

Answer 4

Suppose you have a simple table with the columns below, here you only indexed Id:

 Id (Int), Telephone_Number (Int), Name (VARCHAR), Address (VARCHAR) 

Imagine that you need to fulfill the query below and check if its index is using, and whether it works efficiently without I / O calls or not. Remember that you just created an index on Id .

 SELECT Id FROM mytable WHERE Telephone_Number = '55442233'; 

When you check the performance for this query, you will be disappointed, since Telephone_Number not indexed, this requires fetching rows from the table using I / O calls. Thus, this is not coverage indexed, since there is some column in the request that is not indexed, which leads to frequent I / O calls.

To make this a private index, you need to create a composite index on (Id, Telephone_Number) .

See this blog post for more details: https://www.percona.com/blog/2006/11/23/covering-index-and-prefix-indexes/