Indexes and Performance Tradeoffs | Dataford Interview Questions

What are indexes, and how do they impact write performance versus read performance in a database used for clinical data at Medpace?

For the hands-on portion, use the supplied index statistics to classify indexes according to their observed read activity and write-maintenance operations.

Output

One row per index on clinical_records
Return index_name, read_hit_ratio, maintenance_operations, and performance_assessment
Sort by performance_assessment priority, then index_name alphabetically

Schema

index_usage_stats

Column	Type	Description
index_namePK	VARCHAR(100)	Name of the database index
table_name	VARCHAR(100)	Table associated with the index
idx_scan	BIGINT	Number of index scans
idx_tup_read	BIGINT	Index entries returned by scans
idx_tup_fetch	BIGINT	Table rows fetched through the index
inserts	BIGINT	Rows inserted during the observation period
updates	BIGINT	Rows updated during the observation period
deletes	BIGINT	Rows deleted during the observation period

Your question is Indexes and Performance Tradeoffs. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem