What is a Covered Index?

by

A 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.

Here’s an article: Index Covering Boosts SQL Server Query Performance on the subject.

More Related Contents:

  1. How does database indexing work? [closed]
  2. sql unique constraint on a 2 columns combination
  3. Two single-column indexes vs one two-column index in MySQL?
  4. VARCHAR as foreign key/primary key in database good or bad?
  5. Insert into … values ( SELECT … FROM … )
  6. Refactor a PL/pgSQL function to return the output of various SELECT queries
  7. What is the difference between Left, Right, Outer and Inner Joins? [duplicate]
  8. IN vs OR in the SQL WHERE Clause
  9. Index for finding an element in a JSON array
  10. Creating an index on a table variable
  11. Where is |DataDirectory| defined?
  12. MySQL not using indexes with WHERE IN clause?
  13. When to use SQL Table Alias
  14. What is the difference between using a cross join and putting a comma between the two tables?
  15. When or why would you use a right outer join instead of left?
  16. PostgreSQL parameterized Order By / Limit in table function
  17. Pivot Table and Concatenate Columns
  18. How do I interpret precision and scale of a number in a database?
  19. What are the benefits of using database cursor?
  20. What is semi-join in database?
  21. Procedure or function !!! has too many arguments specified
  22. The backend version is not supported to design database diagrams or tables
  23. SQL query that gives distinct results that match multiple columns
  24. Android multiple databases open
  25. What is it exactly a BLOB in a DBMS context
  26. Composite Primary Keys : Good or Bad?
  27. Is there a version control system for database structure changes?
  28. Using a database table as a queue
  29. Temporal database design, with a twist (live vs draft rows)
  30. Executing SQL query on multiple databases

Leave a Comment