What is an Index in SQL ? Explain their advantages, and disadvantages.

An index in SQL is a database object that speeds up data retrieval from a table. It works like the index at the back of a book: instead of reading every page to find a topic, you look it up and go straight to the right pages.

A database index lets the engine find specific rows without scanning the whole table. It matters most on large tables with thousands or millions of records.

Single-column index

A single-column index is created on one column. It helps when that column is often used in WHERE, JOIN, or ORDER BY.

Suppose the Sales table holds thousands of transactions and you often search by product_id:

This creates an index named idx_product_id on the product_id column.

Now consider this query:

Without the index, the database may have to scan the entire Sales table to find matching rows. With it, the database can use the index to locate those rows directly and scan far less data.

Multi-column index (composite index)

A multi-column index, also called a composite index, is created on two or more columns. It helps when queries often filter or sort on those columns together.

This indexes both product_id and quantity. For this query:

the database can use the composite index to find sales of product 101 with a quantity above 10.

Column order matters. An index on (product_id, quantity) generally helps queries that filter on product_id, or on both columns. It is usually less effective for queries that filter only on quantity.

Unique index

A unique index rejects duplicate values in the indexed column or combination of columns.

This stops two employees from sharing the same email address, subject to how the database handles NULL values. A unique index enforces data integrity and also supports fast lookups.

Advantages

Indexes speed up lookups and WHERE filtering, and they can improve joins between large tables. They can also reduce sorting work for some ORDER BY queries. All of this comes from avoiding full table scans where the index is usable.

Disadvantages

Every INSERT, UPDATE, and DELETE has to maintain the indexes too, so writes get slower. Indexes also take up storage and need some monitoring and maintenance. Adding indexes that no query uses increases write overhead without guaranteeing faster reads.

Practical example in Appian

Suppose an Appian application manages 100,000 financial transactions. A dashboard often retrieves pending transactions with this query:

If those two columns are usually filtered together, a composite index is worth considering:

This may help the database find the relevant transactions faster, but the actual benefit depends on how many rows match, the table size, and the query execution plan.

In Appian, indexes help when tuning queries against large tables and views, including the data sources behind non-synced record types.

Interview takeaway

An index is a database structure that helps retrieve records faster. It can cover one column or several, and a unique index also prevents duplicates. Indexes generally improve reads but add storage and slow down writes, so base them on real query patterns: index columns that are frequently searched, filtered, joined, or sorted, and skip the rest.