How do you create an index on a table column?
How to Create an Index on a Table Column in MySQL
Creating an index in MySQL improves the speed of data retrieval by allowing MySQL to quickly locate rows without scanning the entire table. You can create different types of indexes depending on your requirements.
- 1
Using CREATE INDEX:
CREATE INDEX index_name ON table_name (column_name); - 2
Using ALTER TABLE:
ALTER TABLE table_name ADD INDEX index_name (column_name);
You can also create multi-column (composite) indexes when your queries filter or sort using multiple columns together.
- 1
When a column is frequently used in WHERE filters.
- 2
When performing JOINs on a specific column.
- 3
When using ORDER BY or GROUP BY on large datasets.
- 4
When enforcing uniqueness (UNIQUE index).
Index creation is straightforward, but choosing the right columns to index is critical to balancing performance and storage.
Scenario Questions
0-2 years experience
- 1We have a `users` table with 1 million rows and a query that filters by `email`. How would you add an index to improve this query, and what exact SQL would you run?
- 2If you create an index on the `created_at` column of a log table, what effect does it have on INSERT performance?
- 3After adding an index, you notice the query plan hasn't changed. What could be the reason?
2-5 years experience
- 1Our product team added a composite index on (`first_name`, `last_name`) but searches by last name alone are still slow. How would you diagnose and fix this?
- 2During a deployment we added a new index on a high‑traffic table and observed a spike in latency for writes. Explain why this happened and what options you have to mitigate it.
- 3You need to add an index to a partitioned table without downtime. Walk me through your approach.
5-8 years experience
- 1We have a multi‑tenant SaaS with separate schemas per tenant, each with an `orders` table. How would you design an indexing strategy that balances query performance and storage across thousands of tenants?
- 2Our monitoring shows that a certain index is rarely used but consumes a lot of disk. How would you evaluate whether to drop it, and what steps would you take to safely remove it?
- 3Explain how you would use covering indexes and index hints to optimize a complex reporting query that joins three large tables.
8+ years experience
- 1Our organization is migrating from MySQL 5.7 to 8.0 and wants to consolidate many similar indexes across services. How would you lead a cross‑team effort to audit, refactor, and standardize index usage while minimizing risk?
- 2Design a process for continuous index health monitoring and automated recommendations in a large microservices environment with dozens of MySQL clusters.
- 3When introducing a new feature that adds a searchable JSON column, how would you decide between generated virtual columns with indexes versus full‑text indexes, considering future schema evolution and operational overhead?
Follow-up Questions
- What are the trade‑offs between a single‑column index and a composite index in this scenario?
- How would you verify that the index is actually being used in production?
- Can you describe how you would roll back an index change if it caused issues?