The main difference between primary and secondary index is that the primary index is an index on a set of fields that includes the primary key and does not contain duplicates, while the secondary index is an index that is not a primary index and can contain duplicates.
Indexing is a process that helps to optimize the performance of a database. It reduces the number of disk accesses to process a query. An index is a data structure that is used to find and access data available in a table of a database faster. Generally, an index has two sections: the search key and the data reference. The search key contains a copy of a primary key or a candidate key of the table. As they are stored in an ordered manner, it is easier to access the data easily. Data reference contains a pointer. It stores the address of the disk block corresponding to that key. Furthermore, there are various types of indexing methods. Two of them are primary and secondary index.
Key Areas Covered
1. What is Primary Index
-Definition, Functionality
2. What is Secondary Index
–Definition, Functionality
3. Difference Between Primary and Secondary Index
-Comparison of key differences
Key Terms
Dense Index, Indexing, Primary Index, Primary Key, Secondary Index, Spare Index
What is Primary Index
If the index is based on the primary key, it is called the primary index. Those keys are unique to each record. Furthermore, it contains 1:1 relation between the records. Searching data using the primary index is efficient because it stores data in the sorted order.
Mainly, there are two types of indexes as a dense index and spare index. In a dense index, the number of records in the index table is similar to the number of records in the main table. As the index consists of an index record for each search key value in the data file, the search operation is much faster. Secondly, in the sparse index, the index record appears for few items and each item points to a block. In this index type, the table increases with the size of the table. The primary memory stores the mapping of addresses. However, the secondary memory performs the searching for the actual data depending on the addresses of the mapping. Increase in the mapping size reduces the speed of fetching the addresses. Therefore, the space index is not an efficient indexing method.
What is Secondary Index
The secondary index is an index type that helps to reduce the size of mapping by introducing another level of indexing. At the initial stage, it selects a range for the columns. Therefore, the mapping size of the first level becomes smaller. Then, this index method reduces each range into smaller ranges. Generally, the primary memory stores the first level mappings to fetch addresses faster. Furthermore, the secondary memory stores the mapping of the second level and the actual data.
Difference Between Primary Index and Secondary Index
Definition
A primary index is an index on a set of fields that includes the unique primary key and is guaranteed not to contain duplicates. In contrast, a secondary index is an index that is not a primary index and may have duplicates.
Order
The primary index requires the rows in data blocks to be ordered on the index key while the secondary index does not have an impact on how the rows are actually organized in data blocks.
Number of indexes
Moreover, there is only one primary index, while there can be multiple secondary indexes.
Duplicates
There are no duplicates in the primary index while there can be duplicates in the secondary index.
Conclusion
In brief, there are various types of indexes, and two of them are primary and secondary index. The main difference between primary and secondary index is that the primary index is an index on a set of fields that includes the primary key for the field and does not contain duplicates, while the secondary index is an index that is not a primary index and which can contain duplicates.
References:
1.Www.javatpoint.com, Available here.
Image Courtesy:
1.”Btree index” (CC BY-SA 3.0) via Commons Wikimedia
Leave a Reply