34

I am very confused to understand the difference between sparse index and dense index. Can you explain the difference between them ?

Sabir Al Fateh
  • 1,743
  • 3
  • 20
  • 27

2 Answers2

29

Dense Index

An index record is created for every row of the table.

  • Records can be located directly as each record of the index holds the search key value and the pointer to the actual record.

Visualisation of Index Record as a two column table, first column has name of the cities and second column has a direct pointer to the actual row of the cities table

Sparse Index

Index records are created only for some of the records. To locate a record:

  • find the index record with the largest search key value <= the search key value we are looking for
  • start at that record pointed to by this index record and proceed along the pointers in the file (that is, sequentially) until we find the desired record

Visualisation of Index Record as a two column table, first column has name of the cities and second column has a direct pointer to the actual row of the cities table but not all entries in index table correspond to the main table

While Dense Indexes are great for search and select operations they are more expensive to maintain when compared to Sparse indexes.

Reference Notes

Abhinav Srivastava
  • 840
  • 10
  • 17
  • 6
    I'd note that "faster" here is specifically *faster selects*. Sparse could be described as *faster inserts, deletes and updates* – Caleth Jan 04 '19 at 13:23
  • 1
    @Caleth that's a fair point but I believe the purpose of indexing is to attend _faster selects_ thus in general dense indices are indeed faster. Sounds right? – Abhinav Srivastava Jan 29 '19 at 10:28
  • 1
    My point is that there isn't a platonic "faster" for operations on a table, there are *multiple* measures that you can care about. Sometimes the business need is that inserts are as fast as possible, and other queries don't matter so much. – Caleth Jan 29 '19 at 10:32
  • I think Sparse Indexing will be much faster because of sequential data in it and we can apply binary search on it. For Dense Index, we need sequential search and it will cost in terms of time. – msmq Mar 28 '19 at 07:20
  • @msmq - Seems like you have it backward. A Dense Binary index has no sequential searching. – Rick James Dec 20 '19 at 20:50
  • I'm a little confused about dense indexes. What's the point of having a dense index if we can simply use the data itself to lookup the value? Is it the fact that the data might be unsorted while the index must be sorted? – CrazyMan Apr 10 '23 at 22:25
4

In Dense Index, an index entry appears for every search-key whereas for Sparse index, an index entry appears for only some of the search-key values.