In MySQL, keys (PRIMARY KEY, UNIQUE, FOREIGN KEY, and INDEX) play a crucial role in how the optimizer chooses execution plans, how efficiently queries are executed, and how fast rows can be located or enforced for integrity. Proper key definitions directly impact performance, storage, and MySQL’s ability to optimize queries.

1. PRIMARY KEY and Its Optimization Impact

1
Automatically creates a UNIQUE, NOT NULL index.
2
Defines the clustering key in InnoDB, meaning all table data is physically organized based on the PRIMARY KEY.
3
Smaller, stable PRIMARY KEY columns (e.g., INT) improve both read and write performance.
4
Queries using the PRIMARY KEY usually have the fastest lookup path.

2. UNIQUE Keys and Query Optimization

1
Enforce uniqueness while also creating a unique index.
2
Allow MySQL to stop searching as soon as a match is found (because duplicates cannot exist).
3
Help the optimizer choose efficient plans for equality-based filters.

3. FOREIGN Keys and Index Requirements

1
MySQL automatically indexes child table columns used in a foreign key constraint if no index exists.
2
Indexes on both parent and child ensure fast validation of INSERT, UPDATE, and DELETE operations.
3
Improve JOIN performance between parent and child tables.

4. Regular Indexes (Non-unique) and Optimization

1
Speed up WHERE, ORDER BY, and GROUP BY operations.
2
Allow the optimizer to avoid full table scans.
3
Support multicolumn index strategies such as leftmost-prefix optimization.
4
Poorly chosen indexes can slow down writes and waste storage.

5. Example: Effect of Keys on Query Optimization

6. How Keys Affect the Optimizer

1
The optimizer evaluates available indexes to choose the cheapest access path.
2
Covering indexes allow MySQL to satisfy queries without reading table data.
3
Well-designed keys reduce temporary tables and sort operations.
4
Bad or missing keys often lead to full table scans and slow joins.

In summary, key definitions are central to indexing and optimization in MySQL. Choosing appropriate PRIMARY, UNIQUE, FOREIGN, and secondary indexes ensures efficient execution plans, data integrity, and faster query performance.

Difficulty: 6/10

Topics: index key selection, query execution plans, composite index ordering

Scenario Questions

0-2 years experience

1
You're adding a WHERE clause on user_id and created_at to a slow query — you create an index on user_id first, then on created_at. The query is still slow. What’s the most likely mistake?
2
A junior engineer adds an index on a low-cardinality column like 'is_active' and claims it sped up queries. Why is that probably wrong, and what should they check instead?

2-5 years experience

1
Our search endpoint for orders by customer_id and status started timing out after we added 5M new records. The index exists on customer_id, but not status. How would you debug this and what index change would you propose?
2
We added a composite index on (region, city, zip) for a user lookup, but now queries filtering only on zip are slower. Why, and how would you fix it without breaking existing queries?

5-8 years experience

1
We have a high-write OLTP table with 10+ indexes. Queries are fast, but inserts are getting 30% slower. How would you evaluate which indexes to drop or restructure, and what metrics would you track?
2
A critical reporting query joins three large tables and filters on five columns. You can only create two composite indexes due to storage limits. How do you decide which columns to include in each, and how do you validate the tradeoff?

8+ years experience

1
We’re migrating from a monolithic MySQL DB to a sharded architecture. Our legacy queries rely on composite indexes that span shard keys. How do you redesign indexing strategy without breaking existing SLAs or requiring full app rewrites?
2
A decade-old reporting system uses indexes on VARCHAR(255) fields for partial text matching. Now we’re scaling to petabytes of data and seeing index bloat. What’s your long-term strategy to modernize this without disrupting analytics pipelines?

Follow-up Questions

❓How would you verify if your index is actually being used?
❓What happens if you add a new filter condition that isn't in the index?
❓When would you choose a single-column index over a composite one?