RDMS related interview questions

Created on: Jan 15, 2025

Difference between clustered and non clustered index in mysql ?
- Clustered indexes maintain the order of rows in a table, while non-clustered indexes do not.
- A table can only have one clustered index, but it can have multiple non-clustered indexes.
- Non-clustered indexes are stored in a separate space, so they require extra space.
- Clustered indexes are faster than non-clustered indexes.
- Clustered indexes are used to specify the physical order of records, while non-clustered indexes are used to improve query performance
- Primary key provides clustered index while unique key provides non clustered index. something

What is sharding ? Sharding is a type of DataBase partitioning in which a large database is divided or partitioned into smaller data and different nodes.

Example: Let’s assume the e-commerce platform has a Users table with millions of rows. If all user data is stored in one server, it could slow down as more users join. We can split the user for different location with multiple server option.

Region	Shard	Server
US	Shard 1 (UserID 1-1000)	users_server_us_1
	Shard 2 (UserID 1001-2000)	users_server_us_2
Europe	Shard 1 (UserID 1-1000)	users_server_europe_1
	Shard 2 (UserID 1001-2000)	users_server_europe_2
Asia	Shard 1 (UserID 1-1000)	users_server_asia_1
	Shard 2 (UserID 1001-2000)	users_server_asia_2

Benefits:

🖥️ Scalability: Sharding allows you to distribute the data across multiple servers (shards), enabling horizontal scaling.
⚡ Improved Performance and Reduced Latency By spreading data across multiple shards, queries can be processed in parallel, reducing the query response time.
🔐 Increased Availability and Fault Tolerance Sharding can increase **system availability** by isolating failures to specific shards.
Reduced Costs: Sharding allows the system to scale horizontally, which can be more cost-effective than scaling vertically by upgrading hardware.

What is replication in db and master slave architecture ?