Indexing in Database

What is an index in a database?

A data structure that improves the speed of data retrieval operations on a table.

Why are indexes used?

To speed up SELECT queries and WHERE clause filtering.

What is the trade-off of using indexes?

They improve read performance but slow down INSERT, UPDATE, and DELETE operations.

What is a primary index?

An index created on the primary key of a table, automatically enforced by the database.

What is a secondary index?

An index created on a column that is not the primary key.

What is a unique index?

An index that ensures all values in the indexed column are unique.

Can a table have multiple indexes?

Yes, a table can have multiple indexes on different columns.

What is a composite index?

An index created on two or more columns of a table.

When should you use a composite index?

When queries frequently filter or sort by multiple columns together.

What is a clustered index?

An index where the table’s rows are stored in the order of the index itself.

How many clustered indexes can a table have?

Only one, because the data rows can be sorted in only one order.

What is a non-clustered index?

An index that maintains a separate structure from the actual table data and points to the data rows.

How many non-clustered indexes can a table have?

Many, depending on the database system.

What is a covering index?

An index that contains all the columns needed by a query, avoiding access to the table data.

What is a B-tree index?

A balanced tree structure commonly used to maintain sorted data and support range queries.

What is a hash index?

An index based on hash tables that is optimized for equality comparisons, not range queries.

What is a full-text index?

An index designed for efficient text searching within string columns.

What is a bitmap index?

An index that uses bitmaps and is suitable for columns with low cardinality.

What is cardinality in indexing?

The uniqueness of data in a column; high cardinality means many unique values.

When should you avoid indexing a column?

When the column has low selectivity or frequent updates.

What is index selectivity?

The ratio of unique indexed values to the total number of rows.

What is an index scan?

When the database reads the entire index to find matching rows.

What is an index seek?

When the database navigates directly to the relevant part of the index for efficient access.

What is an index-only scan?

A query that can be satisfied using just the index without accessing the table.

What does EXPLAIN or EXPLAIN PLAN do?

It shows how the database query planner intends to execute a query, including index usage.

What is index fragmentation?

The scattering of index pages across storage, which can slow down performance.

How do you reduce index fragmentation?

By rebuilding or reorganizing the index.

What is a partial index?

An index that only includes rows matching a certain condition.

What is a functional index?

An index on the result of a function or expression applied to a column.

What is a descending index?

An index that sorts values in descending order for performance optimization in queries with DESC.

Do indexes consume disk space?

Yes, indexes use additional storage to maintain the index data structures.

Can indexes be created on views?

Yes, through indexed (materialized) views in some databases.

Can NULL values be indexed?

Yes, but behavior may vary depending on the database engine and index type.

What is the difference between dense and sparse indexes?

Dense indexes have entries for every record; sparse indexes only for some records.

What is an index hint?

A directive given in a SQL query to force the use or avoidance of a specific index.

How does indexing affect JOIN operations?

Proper indexes on join columns can greatly improve join performance.

How are indexes created in SQL?

Using CREATE INDEX index_name ON table_name(column_name);

How do you drop an index in SQL?

Using DROP INDEX index_name;

Can indexes be automatically created?

Yes, primary keys and unique constraints automatically create indexes.

What is a virtual index?

A theoretical index created to test query performance without physically building the index (available in Oracle).

What is multi-column index ordering important for?

It affects which combinations of columns are optimized by the index.

What is a spatial index?

An index designed to efficiently query spatial data (e.g., geometries, locations).

What is an inverted index?

An index that maps content to its location, commonly used in full-text search engines.

Which operations are negatively affected by too many indexes?

INSERT, UPDATE, DELETE because indexes must be updated.

Should every column be indexed?

No, only columns frequently used in search, join, or sort operations should be indexed.

Can you rename an index?

Yes, in some databases using ALTER INDEX or equivalent syntax.

How does indexing support sorting?

Indexes maintain ordered data which can eliminate the need for extra sorting.

What is an expression index?

An index on the result of a computation, like LOWER(name) or YEAR(date).

Are indexes shared across tables?

No, indexes are specific to a single table.

What is the benefit of indexing foreign keys?

Improves JOIN and DELETE performance and maintains referential integrity efficiently.