Clustered indexes vs. Secondary indexes

{question}

Which indexes are clustered indexes, and which ones are secondary indexes?

{question}

{answer}

An index is an on-disk structure associated with a table or view designed to enable a very efficient search of rows from the table or view. An index contains keys built from one or more columns in the table or view.

There are 2 main types of indexes that affect the scanning of records: clustered, and non-clustered also known as secondary indexes.

Clustered indexes:

Clustered indexes sort and store the data rows in the table or view based on their key values.

There can be only one clustered index per table because the data rows can be stored in only one order.

In SingleStore, the primary key on rowstore tables and the clustered columnstore (aka sort key) are clustered indexes.

It is generally faster to read from a clustered index if all the columns have to be retrieved. It is not needed to traverse back to the table from the index.

Example:

How to create clustered indexes:

• • • On Rowstore tables:
    • On Columnstore tables:

How to check the clustered index:

• • • Execute  ‘SHOW INDEXES IN <table_name>’;
    • The column ‘Non_unique’ will be marked ‘0’ for Primary Key/clustered index.

Secondary/Nonclustered indexes:

A non-clustered index is an additional index, which has a structure separate from the data rows. It contains the nonclustered index key values, and each key-value entry has a pointer to the data row that contains the key-value rather than the actual data itself. So, this is a pointer to the record within the disk file, where the data is stored.

There can be multiple secondary indexes on a table.

In SingleStore, these are called secondary indexes. Any key/index other than the primary key or clustered columnstore key can be considered non-clustered, including secondary hash indexes.

Secondary indexes are generally effective if the requirement is to look for 10% of the table at most.

• Example:

• • How to create non-clustered/secondary indexes:
    • On Rowstore tables:
    • On Columnstore tables:

How to check the secondary index:

• • • Execute  ‘SHOW INDEXES IN <table_name>’;
    • The column ‘Non_unique’ will be marked ‘1’ for secondary indexes.

 

{answer}