Answer

Indexes are special data structures (most often based on B-trees) that serve to quickly search for data by a specific column or set of columns in a table. Indexes accelerate selection, sorting, and filtering, reducing the number of rows scanned.

Types of indexes:

• Regular (B-tree, hash)
• Composite (multi-column)
• Unique (ensure value uniqueness)
• Covering (covering)
• Full-text (full-text)

Indexes speed up:

• WHERE ... = ...
• JOIN on indexed columns
• ORDER BY and GROUP BY on indexed columns

Can slow down:

• Insertion, update, deletion — indexes require additional operations to maintain their structure.
• If queries often utilize columns without indexes, indexes will be useless, and during bulk inserts — even harmful.

Example of creating an index:

CREATE INDEX idx_user_email ON users (email);

Example where index does not help:

SELECT * FROM users WHERE lower(email) = 'test@example.com';
-- if the index is built on email, but the query uses the function lower(email), the index is not used!

Trick question

If an index is added to all columns in a table, will the execution of all SELECT queries always speed up?

Answer:
No. Indexes speed up only those queries where filtering or sorting occurs strictly on the indexed column without using functions or operations that hinder index usage. An excessive number of indexes not only slows down INSERT/UPDATE/DELETE but also takes up a lot of space, and some complex queries may completely bypass indexes (for example, when scanning a range with an expression).

Example:

SELECT * FROM orders WHERE year(order_date) = 2023;
-- if the index is only on order_date, the index does not work due to the function year()

---

History

In the email system, a trigger was created that generated an index for each frequently used field. After six months, system performance dropped — each insertion or change of a row took 4-5 times longer. After an audit, the number of indexes was reduced, and the system accelerated.

---

History

In the advertising platform, SELECT queries with a filter on substring(url, 1, 10) were common. Despite the index on url, SQL did not use the index due to the substring function. The solution was to introduce a separate field for such selection and an index on it.

---

History

In the loyalty program, a composite index was created on the fields (customer_id, shop_id). When querying only by shop_id, the index was not applied, and a full table scan occurred. This led to the loss of the index benefit when calculating bonuses. Optimization helped: a separate index on shop_id.