73

Can any one tell me what's the exact difference between Primary index & Secondary index? What are the different indexes that comes under category of primary & secondary index? And what's advantages of using primary index over secondary index & vice versa?

user3099630
  • 851
  • 1
  • 6
  • 6
  • Do you mean primary and foreign keys? – juergen d Dec 29 '13 at 11:31
  • @juergend, not primary & foreign key but just the index. – user3099630 Dec 29 '13 at 11:38
  • There is no such thing as a primary or secondary index in Oracle. Are you confusing that with unique and non-unique indexes? –  Dec 29 '13 at 13:08
  • See also this post https://stackoverflow.com/questions/20824686/what-is-difference-between-primary-index-and-secondary-index-exactly – Athanassios Jan 26 '20 at 14:22
  • 3
    This is NOT a duplicate question. MySQL makes confusing references to primary and secondary indexes when discussing generated calculated columns on INNODB databases. – William Entriken Jul 28 '20 at 15:25
  • It also **does** exist in the Oracle docs, I think it's not a duplicate as well. https://docs.oracle.com/cd/E17275_01/html/programmer_reference/am_second.html – aderchox May 20 '23 at 07:47

1 Answers1

120

Primary index:

A primary index is an index on a set of fields that includes the unique primary key for the field and is guaranteed not to contain duplicates. Also Called a Clustered index. eg. Employee ID can be Example of it.

Secondary index:

A Secondary index is an index that is not a primary index and may have duplicates. eg. Employee name can be example of it. Because Employee name can have similar values.

The primary index contains the key fields of the table. The primary index is automatically created in the database when the table is activated. If a large table is frequently accessed such that it is not possible to apply primary index sorting, you should create secondary indexes for the table.

The indexes on a table have a three-character index ID. '0' is reserved for the primary index. Customers can create their own indexes on SAP tables; their IDs must begin with Y or Z.

Mouna
  • 3,221
  • 3
  • 27
  • 38
Kumod Singh
  • 2,113
  • 1
  • 16
  • 18
  • 26
    To clarify "*If a large table is frequently accessed such that it is not possible to apply primary index sorting, you should create secondary indexes for the table.*": If you frequently perform queries that are not based on the primary index of a table, but rather on other column(s), then for performance, create a secondary index on that column (or set of columns). For example, consider a table of Employees. You commonly want to query by a person's Name, but the Name is typically not the Primary index, because it is not unique (two people with same name). So make secondary index on Name. – ToolmakerSteve Sep 23 '16 at 18:41
  • 3
    (I realize that the answer talked about Name as secondary index; my clarification is to tie the sentence I quoted back to that sentence about Name as secondary index.) – ToolmakerSteve Sep 23 '16 at 18:45
  • @ToolmakerSteve Technically can a secondary index work in absence or a primary index? I see the MySQL creates one internally even if you don't. You can only have secondary index over the primary index. – piepi Jan 19 '21 at 10:22
  • While this seems to explain the details of a secondary index in a specific database, I think most people (like myself) are newer database users and want to know the underlying reason why such a distinction exists. imo, the details written seem pretty trivial. – dtc Jan 19 '21 at 17:38
  • @dtc - [Branko's answer here](https://stackoverflow.com/a/10720128/199364) is a good explanation. Short summary is that the physical organization of the data takes into account the primary index, which can improve performance and memory footprint, compared to use of a secondary index. Nevertheless, if the primary index does not help optimize some query that you do frequently, it is usually worth creating a secondary index designed to help that query. (Read any article about how and when an index speeds up a query; such speed up occurs whether index is primary or secondary.) – ToolmakerSteve Jan 19 '21 at 22:20
  • @piepi - MySQL organizes data internally based on primary index. If there is no specified primary index, it still must decide how to organize (and access) the raw data - presumably this is why it creates some such index. I suspect for any query, it uses primary index of each matching row, to store in an array of "descriptors" of rows that match a query. – ToolmakerSteve Jan 19 '21 at 22:34
  • If the index is created on ordering key attribute it is a primary index, if it is created on ordering non-key attribute it is clustered index, and if it is created on non-ordering key or non-key attribute, it is a secondary index. Hence there can be only one primary or clustered index but not both, whereas more than one secondary index is allowed. – Amit P Jun 01 '22 at 07:32