What is a covered Index?

0 votes
I've just heard the term covered index in some database discussion - what does it mean?
Oct 10, 2018 in Database by Neha
• 6,280 points
49 views

1 answer to this question.

0 votes

covering index is an index that contains all of, and possibly more, the columns you need for your query.

For instance, this:

SELECT *
FROM tablename
WHERE criteria

will typically use indexes to speed up the resolution of which rows to retrieve using criteria, but then it will go to the full table to retrieve the rows.

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

SELECT column1, column2
FROM tablename
WHERE criteria

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.

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

answered Oct 10, 2018 by Frankie
• 9,810 points

Related Questions In Database

0 votes
1 answer

What is a Relationship and what are they?

Relation or links are between entities that ...READ MORE

answered Oct 23, 2018 in Database by DataKing99
• 8,130 points
25 views
0 votes
1 answer

What is a Foreign key?

Foreign key maintains referential integrity by enforcing ...READ MORE

answered Oct 26, 2018 in Database by darklord
• 6,170 points
43 views
0 votes
1 answer

What is Index hunting?

Index hunting is the process of boosting ...READ MORE

answered Nov 9, 2018 in Database by DataKing99
• 8,130 points
346 views
0 votes
1 answer

What is a Join in terms of database?

JOINS are used to combine rows from ...READ MORE

answered Nov 13, 2018 in Database by DataKing99
• 8,130 points
51 views
0 votes
1 answer

How to connect Java program to the MySQL database?

You can connect your Java code with ...READ MORE

answered May 11, 2018 in Java by parth295
• 4,640 points
195 views
0 votes
1 answer

Is SELECT * harmful in Database?

There are really three major reasons: Inefficiency in ...READ MORE

answered Sep 7, 2018 in Database by DataKing99
• 8,130 points
46 views
0 votes
1 answer

Difference between single and double quotes in SQL

Single quotes are used to indicate the ...READ MORE

answered Sep 11, 2018 in Database by CodingByHeart77
• 3,690 points
87 views
0 votes
1 answer

Can different databases use different name quotes?

This use of quotes is called delimited ...READ MORE

answered Sep 11, 2018 in Database by CodingByHeart77
• 3,690 points
35 views
0 votes
1 answer

Which one is suitable for real time big database- Primary Key or Unique Index?

What is a unique index? A unique index ...READ MORE

answered Oct 12, 2018 in Database by Frankie
• 9,810 points
37 views
0 votes
1 answer

How to schedule a job for SQL query to run daily?

down voteaccepted Expand the SQL Server Agent node ...READ MORE

answered Oct 25, 2018 in Database by Frankie
• 9,810 points
59 views