Index Type

Primary Index

Use for uniquely identifying rows (primary key).

• A primary index is automatically created when you define a primary key in a table.

• It enforces uniqueness and ensures that no two rows have the same value in the primary key column(s).

• In many databases (e.g., MySQL with InnoDB), the primary index is also a clustered index, meaning it determines the physical order of data in the table.
  How it works

• The primary index stores the primary key values in a sorted order (e.g., a B-tree structure).

• When you query using the primary key, the database can quickly locate the row.

• Use it for the column(s) that uniquely identify each row in the table (e.g., id).

Example:
CREATE TABLE employees (
id INT PRIMARY KEY, -- Primary key (and primary index) is created automatically
name VARCHAR(100),
department VARCHAR(50)
);

Query using the primary key
SELECT * FROM employees WHERE id = 101;

Secondary Index

Use for speeding up queries on non-key columns.

• A secondary index is any index that is not the primary index.

• It is created on non-primary key columns to speed up queries that filter, sort, or join on those columns.

• Secondary indexes are typically non-clustered, meaning they store a separate data structure pointing to the actual rows.

• The secondary index stores the indexed column values along with a pointer to the corresponding row in the table.

• When you query using the indexed column, the database uses the secondary index to locate the rows.

• Use it for columns frequently used in WHERE, JOIN, ORDER BY, or GROUP BY clauses.

Example:
CREATE INDEX idx_department ON employees (department); -- Secondary index
SELECT * FROM employees WHERE department = 'Sales';

Unique Index

Use to enforce uniqueness in a column.

• A unique index ensures that all values in the indexed column(s) are unique.

• It is similar to a primary index but does not have to be the primary key.

• The unique index prevents duplicate values from being inserted into the indexed column(s).

• It can be created on one or more columns.

• Use it to enforce uniqueness in a column (e.g., email, username).

Example: CREATE UNIQUE INDEX idx_email ON employees (email); -- Unique index
Attempting to insert duplicate values will result in an error:

INSERT INTO employees (id, name, email) VALUES (1, 'John', '[e-mail]');
INSERT INTO employees (id, name, email) VALUES (2, 'Jane', '[e-mail]'); -- Error: Duplicate email

Composite Index

Use for queries involving multiple columns.

• A composite index is an index created on multiple columns.

• The order of columns in the index matters: the leftmost column is used first for searching.

• The composite index stores a combination of values from the indexed columns.

• It is useful for queries that filter or sort by multiple columns.

• Use it when queries frequently filter or sort by multiple columns together.

Example:
CREATE INDEX idx_dept_salary ON employees (department, salary); -- Composite index

Query using the composite index:

SELECT * FROM employees WHERE department = 'Sales' AND salary > 50000;

Clustered Index

Use for range queries or sorting (one per table).

• A clustered index determines the physical order of data in a table.

• A table can have only one clustered index (usually the primary key).

• The data rows are stored in the order of the clustered index.

• The clustered index stores the actual data rows in the index structure (e.g., a B-tree).

• When you query using the clustered index, the database can retrieve the data directly.

• Use it for columns frequently used in range queries (e.g., BETWEEN, >, <) or for columns that are often sorted.

Example:

CREATE CLUSTERED INDEX idx_clustered ON employees (id); -- Clustered index

Query using the clustered index:

SELECT * FROM employees WHERE id BETWEEN 100 AND 200;

Full-Text Index

Use for advanced text search in large text fields.

• A full-text index is used for efficient searching of text data (e.g., searching for words or phrases in large text fields).

• It supports advanced text search features like keyword matching, phrase matching, and relevance ranking.

• The full-text index breaks down the text into tokens (words) and stores them in an optimized structure for fast searching.

• It is commonly used with MATCH and AGAINST clauses in SQL.

• Use it for columns containing large text data (e.g., description, comments).

Example:

CREATE FULLTEXT INDEX idx_description ON products (description); -- Full-text index

Query using the full-text index:

SELECT * FROM products WHERE MATCH(description) AGAINST('database');

When to Use Which Index?

Scenario Recommended Index
Uniquely identifying rows ==> Primary Index
Speeding up queries on non-key columns ==> Secondary Index
Enforcing uniqueness ==> Unique Index
Filtering/sorting by multiple columns ==> Composite Index
Range queries or sorting ==> Clustered Index
Searching text data ==> Full-Text Index