The Slow Search Problem

Ankit built a social media app, but as users increased, searches became painfully slow.

Every time someone searched for a username, the database scanned millions of records. This wasn’t sustainable.

The solution? Indexing and efficient database design. With the right optimizations, queries became lightning-fast.

What is Database Design?

Database design defines how data is structured, stored, and optimized for efficiency.

A well-designed database ensures:

• Fast queries – Data retrieval is quick and efficient.

• Scalability – Handles growing user loads.

• Consistency – Ensures data integrity.

Indexing – Speeding Up Queries

1. What is Indexing?

Indexing is like a book’s table of contents—it helps locate data quickly without scanning the entire database.

Indexes store pointers to data, reducing query execution time significantly.

2. Types of Indexing

• Organizes data hierarchically for quick lookups.

• Ideal for range queries (e.g., finding users between ages 20 and 30).

✔ Pros: Fast search, insert, and delete operations. ✖ Cons: Slight overhead in maintaining balance.

• Uses a hash function to map keys to locations.

• Best for exact match queries (e.g., finding a user by ID).

✔ Pros: Extremely fast for key-based lookups. ✖ Cons: Not ideal for range queries or sorting.

Normalization vs. Denormalization

1. What is Normalization?

Normalization organizes data into smaller tables to eliminate redundancy.

✔ Pros: Prevents data duplication, ensures consistency. ✖ Cons: Increases the number of joins, which can slow down queries.

• User Table (user_id, name, email)

• Orders Table (order_id, user_id, product_id)

2. What is Denormalization?

Denormalization combines tables to reduce joins and speed up queries.

✔ Pros: Faster reads, reduces the need for complex joins. ✖ Cons: Increases redundancy and storage usage.

• Orders Table (order_id, user_name, user_email, product_id)

  But see the issue in here.. user's name and user's email will be stored for each specific order, if there are 1000 orders places by this order, then 1000 times data will be copied same.

Choosing the Right Approach

Feature

Normalization

Denormalization

Storage Efficiency

✔✔✔

✔

Query Speed

✔

✔✔✔

Data Consistency

✔✔✔

✔

Best For

Transactional databases

Analytical databases

Real-World Use Cases

1. E-Commerce Websites

• Use B-Tree Indexing for product searches.

• Normalize user and order data for consistency.

2. Social Media Platforms

• Use Hash Indexing for fast user lookups.

• Denormalize feeds for faster content loading.

3. Financial Systems

• Normalize data for security and accuracy.

• Use B-Tree Indexing for transaction history searches.

Conclusion

A well-designed database is crucial for performance and scalability.

• Indexing speeds up queries (B-Trees for range searches, Hash for exact matches).

• Normalization ensures consistency, while denormalization improves query speed.

Next, we’ll explore Database Scaling Techniques – Sharding, Partitioning, Replication.

Powered by Synscribe