Expected Answer (MySQL 8.4 - InnoDB)

In MySQL’s InnoDB engine, every table has a Clustered Index.

• Primary Key is Clustered: By default, the Primary Key is the clustered index.
• Fallback: If no Primary Key is defined, MySQL uses the first UNIQUE index with all NOT NULL columns. If that doesn’t exist, it generates a hidden 6-byte Row ID (ROWID) to use as the clustered index.

Why It Matters

Since data is physically sorted by the Primary Key in InnoDB, choosing a sequential key (like AUTO_INCREMENT) is vital. Using a random UUID as a PK causes “Index Page Splits” and massive disk I/O as the engine shuffles rows to maintain order.

SQL Example

-- Optimal: Sequential ID
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATETIME
) ENGINE=InnoDB;

-- Sub-optimal: Random UUID (Causes fragmentation)
CREATE TABLE logs (
    id CHAR(36) PRIMARY KEY,
    message TEXT
) ENGINE=InnoDB;

Common Mistakes

• Defining no Primary Key: This forces MySQL to manage a hidden ROWID, which can lead to contention in high-concurrency environments.
• Changing the PK: Changing a clustered key requires rebuilding the entire table.

Follow-up Questions

• What is a Secondary Index in InnoDB? (Answer: A non-clustered index that stores the Clustered Key as a pointer to the data).
• What is the “Clustered Index” in MyISAM? (Answer: MyISAM does not support clustered indexes; data is stored in the order it was inserted).

Expected Answer (SQL Server 2022)

The clustered index is the table. Data is physically stored in the order of the index key.

• One per table: Only one physical order possible.
• Leaf nodes: Contain the actual data.

Why It Matters

Selecting a clustered index is the single most important performance decision in SQL Server. It affects how data is retrieved and how fragmented the table becomes over time. SQL Server 2022 introduces further optimizations for parallel scans on clustered indexes.

SQL Example

-- Creating a table with a Clustered Index on an IDENTITY column (Default)
CREATE TABLE Orders (
    OrderID INT IDENTITY(1,1) PRIMARY KEY, -- Clustered Index created here
    OrderDate DATETIME
);

-- Creating a table with a custom Clustered Index (Non-sequential PK)
CREATE TABLE Events (
    EventID UNIQUEIDENTIFIER PRIMARY KEY NONCLUSTERED,
    EventDate DATETIME INDEX IX_EventDate CLUSTERED
);

Common Mistakes

• Using random GUIDs: Causes massive physical fragmentation (page splits).
• Clustering on large strings: Makes all non-clustered indexes larger (since they include the clustered key).

Follow-up Questions

• What is a Page Split? (Answer: Physical fragmentation when inserting into a full page).
• What is a Heap? (Answer: A table without a clustered index).

Expected Answer (MongoDB 7.0/8.0)

Historically, MongoDB (WiredTiger) used heaps. However, MongoDB 5.3+ introduced Clustered Collections.

• Clustered Collection: Documents are physically stored in the order of the clustered index key (typically _id).
• Advantages: Faster range scans on _id and reduced storage size (less index overhead).

Why It Matters

For high-volume insertion or range-heavy workloads, clustered collections provide significant performance boosts similar to relational clustered indexes. By default, standard collections are still non-clustered heaps.

Example (Creating a Clustered Collection)

db.createCollection("logs", {
   clusteredIndex: {
      "key": { "_id": 1 },
      "unique": true,
      "name": "logs clustered index"
   }
})

Common Mistakes

• Assuming all collections are clustered: You must explicitly enable clustering at creation time.
• Large _id values: Just like in SQL, a very large clustered key increases the size of all secondary indexes.

Follow-up Questions

• Can you cluster on a field other than _id? (Answer: No, currently MongoDB only supports clustering on the _id field).
• When should you NOT use a clustered collection? (Answer: When you rarely perform range queries on _id and want to save the overhead of maintaining the clustered structure).

Expected Answer (Cassandra 5.0)

Cassandra uses a two-part Primary Key to control physical storage:

1. Partition Key: Determines which node in the cluster stores the data.
2. Clustering Columns: Determines the physical sort order of data within a partition on disk. This is Cassandra’s equivalent of a Clustered Index.

Why It Matters

Because data is physically sorted by the clustering columns, range queries (e.g., WHERE date > '2023-01-01') are extremely fast, provided they are within a single partition. Proper selection of clustering columns is the core of Cassandra data modeling.

CQL Example

-- user_id is the Partition Key
-- posted_at is the Clustering Column (physically sorted)
CREATE TABLE user_posts (
    user_id uuid,
    posted_at timestamp,
    content text,
    PRIMARY KEY (user_id, posted_at)
) WITH CLUSTERING ORDER BY (posted_at DESC);

Common Mistakes

• Queries across partitions: Range queries that don’t specify a Partition Key are extremely slow (full cluster scans).
• Too many rows per partition: Partitions should generally stay under 100MB to avoid performance issues.

Follow-up Questions

• What is a composite partition key? (Answer: Using multiple columns to define the partition, e.g., PRIMARY KEY ((tenant_id, user_id), posted_at)).
• Can you change clustering order later? (Answer: No, it requires a table migration).