Understanding Database Indexes: Types, Benefits, Drawbacks, and Optimization Techniques

1. What is an index?

An index is a data structure that sorts the values of one or more columns in a database table, allowing fast access to specific information.

2. Purpose of indexes

An index works like a book's table of contents, enabling quick location of needed content and improving query performance.

3. Advantages

Creating a unique index ensures each row in the table is unique.

Accelerates data retrieval speed.

Speeds up joins between tables.

Reduces time for grouping and sorting during queries.

4. Disadvantages

Creating and maintaining indexes consumes time, which grows with data volume.

Indexes occupy physical storage; larger data means more space.

Indexes can slow down insert, update, and delete operations because they must be maintained dynamically.

5. When to create indexes

Primary keys automatically create a unique index.

Fields frequently used in query conditions should have indexes.

Indexing columns used for sorting greatly improves sort speed.

Columns used for aggregation or grouping.

6. When not to create indexes

Fields that are updated frequently, as each update also updates the index.

Columns not used in WHERE clauses.

Tables with very few rows.

Tables with heavy insert/delete/update activity.

Columns with low cardinality and uniform distribution (e.g., gender), where indexes provide little benefit.

7. Types of indexes

Normal index: basic index without restrictions.

Unique index: column values must be unique and not null; for composite indexes, the combination must be unique.

Primary key index: special unique identifier for a record, cannot be null.

Composite (joint) index: index on multiple columns to accelerate queries.

8. Indexes and SQL optimization

1. Leading wildcard searches cannot use indexes , e.g., name LIKE '%static'.

2. UNION, IN, OR can use indexes; IN is recommended.

3. Negative condition queries cannot use indexes; rewrite as IN. Negative conditions include !=, <>, NOT IN, NOT EXISTS, NOT LIKE, etc.

4. Composite index leftmost prefix principle : for a composite index on (a,b,c), queries on a, (a,b), or (a,b,c) benefit.

5. Place the most selective column at the leftmost position in a composite index.

6. If a composite index (a,b) exists, a separate index on a is unnecessary; similarly for (a,b,c).

7. When mixing equality and inequality conditions, put equality columns first in the index.

8. Range columns can use indexes, but columns after a range column cannot. Only one range column can be used effectively.

9. Perform calculations in the application layer rather than the database layer.

10. Implicit type conversion causes full table scans; e.g., if phone is VARCHAR, the query SELECT * FROM user WHERE phone=13800001234 cannot use an index.

11. Do not index fields that are updated very frequently and have low selectivity.

12. Use covering indexes to avoid row lookups.

13. Indexed columns should be NOT NULL with appropriate defaults.

14. For large‑offset pagination, use keyset pagination: retrieve the max id of the previous page and query the next page with SELECT id, name FROM product WHERE id > 866612 LIMIT 20 instead of SELECT id, name FROM product LIMIT 866613, 20 .

15. Unique business fields, even composite ones, should have unique indexes.

16. Avoid joining more than three tables; ensure join columns have the same data type and are indexed.

17. Use LIMIT 1 when only one row is needed to improve efficiency.

18. Always specify column names in SELECT statements.

19. Minimize sorting on columns without indexes.

20. Prefer UNION ALL over UNION when duplicate rows are not a concern.

21. Use reasonable pagination techniques for better performance.