5. Power of Indexing

Indexes are data structures that speed up data retrieval in databases by reducing the number of rows scanned. However, they come with trade-offs in terms of storage and write performance.

---

1. What is an Index?

An index is like a table of contents for a database. Instead of scanning the entire table, the database looks up the index first to find the required data quickly.

Example Without an Index

A query searching for email='user@example.com' in a Users table without an index scans every row (full table scan):

SELECT * FROM Users WHERE email = 'user@example.com';

If the table has millions of records, this can be slow.

Example With an Index

Creating an index on the email column speeds up queries:

CREATE INDEX idx_email ON Users(email);

Now, the database uses the index to find matching rows quickly.

---

2. Types of Indexes

Index Type	Description	Use Case
Primary Index	Created on primary key (auto-generated in most databases).	Ensures unique identification of records.
Unique Index	Ensures no duplicate values in a column.	Applied on email, username, or social security numbers.
Clustered Index	Controls physical order of rows in storage. Only one per table.	Faster retrieval for range queries (e.g., date-based searches).
Non-Clustered Index	Stores index separately from table data. Multiple indexes per table.	Used for searching on non-primary key columns (e.g., email).
Full-Text Index	Optimized for text search (e.g., words in articles).	Used in search engines, logs, documents.
Composite Index	Index on multiple columns (e.g., firstname, lastname).	Improves searches using both columns together.
Hash Index	Uses a hash function for fast lookups.	Best for exact match queries (e.g., looking up by ID).
Bitmap Index	Uses bitmaps for indexing values.	Ideal for low-cardinality columns (e.g., gender).

---

3. Performance Implications

Advantages

✅ Faster Queries – Reduces search time significantly.
✅ Efficient Sorting – Queries with ORDER BY benefit from indexes.
✅ Speeds Up Joins – Indexes improve JOIN performance in relational databases.

Disadvantages

❌ Slower Inserts, Updates, Deletes – Every change in data requires updating the index, which can slow down write-heavy applications.
❌ Consumes Storage – Indexes take up extra disk space.
❌ Overhead on Frequent Changes – If the data is updated frequently, indexes can become fragmented and require rebuilding.

---

4. When to Use Indexes?

✔ Frequently searched columns (e.g., email, username).
✔ Foreign keys for faster joins.
✔ Columns in WHERE, ORDER BY, GROUP BY queries.

🚫 Avoid indexing on:
✗ Small tables – Full table scans are faster.
✗ Highly volatile columns – Too many updates slow performance.
✗ Low-cardinality columns – Indexing is_active (0/1) is usually wasteful.

---

5. Index Optimization Tips

• Use EXPLAIN (MySQL, PostgreSQL) or EXPLAIN PLAN (Oracle, SQL Server) to analyze query performance.
• Use covering indexes (indexes that include all columns in a query) to avoid extra lookups.
• Limit the number of indexes to avoid performance degradation on writes.
• Use partial indexes (indexing only a subset of rows) where applicable.
• Rebuild indexes periodically to prevent fragmentation (REINDEX, ANALYZE).

Indexes boost read performance but can slow down writes. Proper indexing strategy depends on workload type (read-heavy vs. write-heavy applications).