Clustered vs Non-clustered Index Interview Questions

A clustered index is the table's physical order. You get one per table, often the primary key. A non-clustered index is a separate structure with the key and a pointer back to the row. In InnoDB the table is clustered on the primary key, and secondary indexes store the PK as the pointer. That is why a random UUID primary key can cause page splits and slower inserts. Lookups through a secondary index may need a second hop to the clustered index. I pick a clustered key that is stable and increasing when I can, like an identity or a time-ordered id.

InnoDB clusters the table on the primary key. Secondary indexes store the PK to find the row. A fat random UUID PK makes secondary indexes fat and inserts random. A monotonic PK appends. I would still not use a business email as PK if it can change. I would use a bigserial or ULID depending on the team. SQL Server clustered index is a similar conversation. I would not say 'clustered means faster' with no structure.

No. The clustered index is the table order. You get one. You can have many secondary indexes. If I need another access path I add a non-clustered index. Heap plus non-clustered is a SQL Server option. I would not promise two clustereds. That is a definition check.

If almost every query is by created_at range and the PK is a random UUID, some systems let you cluster on created_at. Then PK lookups need a secondary index. That is a trade-off I would only take with evidence. In InnoDB I usually keep PK clustered and choose a monotonic PK. I would not recluster a live table as my first tuning step.

A non-clustered index finds a key, then the engine must fetch the full row from the clustered index or heap. That extra lookup can make a non-covering index slower than a seq scan. Covering avoids it. I mention this when they ask why SELECT * cannot use a skinny index well. It is the physical reason covering indexes exist.