Covering Index
In most cases, an index is used to quickly locate the data record(s) from which the required data is read. In other words, the index is only used to locate data records in the table and not to return data.
A covering index is a special case where the index itself contains the required data field(s) and can return the data.
Consider the following table (other fields omitted):
| ID | Name | Other Fields |
|---|---|---|
| 12 | Plug | ... |
| 13 | Lamp | ... |
| 14 | Fuse | ... |
To find the Name for ID 13, an index on (ID) will be useful, but the record must still be read to get the Name. However, an index on (ID, Name) contains the required data field and eliminates the need to look up the record.
A covering index can dramatically speed up data retrieval but may itself be large due to the additional keys, which slow down data insertion & update. To reduce such index size, some systems allow non-key fields to be included in the index. Non-key fields are not themselves part of the index ordering but only included at the leaf level, allowing for a covering index with less overall index size.
Read more about this topic: Database Index
Famous quotes containing the words covering and/or index:
“We have good reason to believe that memories of early childhood do not persist in consciousness because of the absence or fragmentary character of language covering this period. Words serve as fixatives for mental images. . . . Even at the end of the second year of life when word tags exist for a number of objects in the child’s life, these words are discrete and do not yet bind together the parts of an experience or organize them in a way that can produce a coherent memory.”
—Selma H. Fraiberg (20th century)
“Exile as a mode of genius no longer exists; in place of Joyce we have the fragments of work appearing in Index on Censorship.”
—Nadine Gordimer (b. 1923)