Multiple Indexes vs Multi-Column Indexes

A database index is a data structure, typically organized as a B-tree, that is used to quickly locate and access data in a database table. Indexes are used to speed up the query process by allowing the database to quickly locate records without having to search through every row of the table.

An index is a data structure in a database that is used to improve the speed of data retrieval operations on a database table. It is a special type of database object that stores the values of one or more columns of a table, and the row ids that point to the physical storage location of the corresponding rows. Indexes are used to optimize the performance of queries, as they allow the database to quickly determine the location of the data that is being requested.

Multiple Indexes

Multiple indexes refer to the creation of individual indexes on different columns in the same table. Each index provides a separate access path to locate data quickly based on the column it’s built on.

In Multiple Indexes

• Each index is created on one column at a time.
• These indexes are independent of each other.
• The database engine can use one or more of these indexes while executing a query, depending on how the query is written and the optimizer's choice.

Example

CREATE INDEX idx_name ON People(name);

CREATE INDEX idx_age ON People(age);

CREATE INDEX idx_dob ON People(date_of_birth);

If a query filters by age, the database uses idx_age.

Useful when you have varied queries like:

SELECT * FROM People WHERE name = 'Alice';

SELECT * FROM People WHERE age = 25;

Advantages

• Flexible: You can create indexes on any number of columns separately.
• Simple to manage: Adding or dropping an index doesn't affect the others.

Disadvantages

• Not efficient for multi-column queries: Using separate indexes for multi-column conditions can be slower than a single multi-column index.
• Higher storage and maintenance cost: Each index adds overhead for storage and updating during data changes.

Multi-Column Indexes

A multi-column index is a single index that includes two or more columns in a specific order. It's used when queries frequently involve multiple columns together. Unlike multiple indexes, multi-column indexes are organized based on the combined data stored in the indexed columns and can only be used to locate data in the indexed columns.

In Multiple Indexes

• The index is sorted and maintained based on the order of columns specified during its creation.
• The database can quickly locate and sort data using the order of columns in the index.

Example

CREATE INDEX idx_name_dob_age ON People(name, date_of_birth, age);

This index helps with queries that filter or sort by:

• name
• name and date_of_birth
• name, date_of_birth, and age

Ideal when your queries consistently use combinations:

SELECT * FROM People WHERE name = 'Alice' AND date_of_birth = '1990-01-01';

Advantages

• Faster Multi-Column Lookups : Especially effective when columns are queried in the same order as defined in the index.
• Efficient for Range Scans and Sorting : Allows the database to quickly perform ordered retrieval and range based queries.
• Reduces Index Count : Combines multiple columns into a single index, minimizing the need for separate indexes and saving storage space.

Disadvantages

• Column order matters: Queries must use the leading columns in the same order to benefit from the index.
• Less flexible: Not as useful if queries vary significantly in which columns are filtered first.
• Harder to manage: Must be carefully planned based on the most common query patterns.

Difference between Multiple Indexes and Multi-Column Indexes