In what cases are indexes used in databases?

Indexes in databases are used to optimize query performance, specifically:

• Data retrieval: Speeding up SELECT operations, especially with WHERE, JOIN, and ORDER BY conditions. An index allows the DBMS to find the needed rows faster without scanning the entire table.
• Sorting: Accelerating sorting with ORDER BY. If there is an index on the sorting columns, the DBMS can use its order, avoiding additional sorting operations.
• Table joins: Speeding up JOIN operations on indexed columns. An index helps quickly find matching rows in related tables.
• Data uniqueness: Ensuring the uniqueness of values in a column (via a unique index).
• Primary keys: The primary key is by default a unique index and is used for quick access to a row by its unique identifier.
• Secondary keys: Used to enforce referential integrity and speed up JOIN operations between related tables.

Examples of columns for indexing:

• Columns frequently searched (WHERE uid = 123).
• Columns used in JOIN conditions (table1.id = table2.ref_id).
• Columns often sorted (ORDER BY created_at DESC).
• Columns with high cardinality (many unique values).

Cases when indexes may be ineffective or unnecessary:

• Very small tables: In small tables, scanning may be faster or comparable in speed to index search.
• Columns with low cardinality: An index on a column with few unique values (e.g., a boolean field TRUE/FALSE) may not provide significant speed-up.
• Frequent write operations (INSERT, UPDATE, DELETE): Indexes require updating when data changes, which can slow down write operations. Not all columns should be indexed indiscriminately.
• Columns in WHERE conditions with functions: If a function is applied to a column in WHERE (e.g., WHERE YEAR(date_column) = 2023), a regular index on date_column is not used. A functional index (if supported by the DBMS) or rewriting the query is needed.

Choosing columns for indexing and the type of index (B-tree, hash, full-text, etc.) depends on data specifics, query patterns, and the DBMS used.