Questions
1 of 19
1What is an index in MySQL and why is it used?
2How do you create an index on a table column?
3What are the different types of indexes available in MySQL?
4How can you view all indexes defined on a table?
5What is the difference between a primary key and a unique index?
6How do indexes improve query performance? Explain with an example.
7What is a composite index, and how does column order affect its usage?
8How do clustered and non-clustered indexes differ in MySQL’s InnoDB engine?
9What is a full-text index and when would you use it?
10Explain the concept of index selectivity and how it impacts performance.
11How does MySQL use indexes internally when executing a JOIN query?
12What is a covering index, and how can it reduce disk I/O?
13What are the trade-offs of adding too many indexes to a table?
14How can partial indexes or prefix indexes be used for large text columns?
15Describe how InnoDB’s clustered index structure affects row storage and lookups.
16What are invisible indexes in MySQL 8.0, and how can they be used for testing?
17How does MySQL optimize queries differently when using B-Tree vs. HASH indexes?
18What happens to indexes during bulk inserts, and how can you optimize performance for such operations?
19How do you identify and remove unused or redundant indexes without affecting application performance?
mysqlmysql
Basics
Databases
Tables
Data Types
Queries
Views
Indexes
Clauses
Regex
Functions and Operators
Keys
Joins
Triggers
01 / 19

What is an index in MySQL and why is it used?

Difficulty: 4/10
B‑tree indexes, query optimization, write overhead

Understanding Indexes in MySQL and Why They Are Used

An index in MySQL is a data structure—typically a B-tree or hash—that improves the speed of data retrieval operations on a table. Indexes allow MySQL to locate rows efficiently without scanning the entire table, making queries significantly faster, especially on large datasets.

Why Indexes Are Used
  1. 1

    To speed up SELECT queries by reducing the number of scanned rows.

  2. 2

    To improve JOIN performance by indexing columns used in join conditions.

  3. 3

    To enforce constraints such as PRIMARY KEY and UNIQUE.

  4. 4

    To accelerate ORDER BY and GROUP BY operations.

  5. 5

    To efficiently filter rows in WHERE clauses.

Common Types of Indexes in MySQL
  1. 1

    PRIMARY KEY – A unique index that identifies each row in a table.

  2. 2

    UNIQUE Index – Ensures all values in a column are unique.

  3. 3

    INDEX (Normal Index) – Speeds up lookup based on a column.

  4. 4

    FULLTEXT Index – Used for text search on large text fields.

  5. 5

    SPATIAL Index – Used for geometry or GIS data types.

When Indexes Improve Performance
  1. 1

    When filtering large tables with WHERE clauses.

  2. 2

    When tables frequently perform join operations.

  3. 3

    When sorting large datasets with ORDER BY.

  4. 4

    When aggregating data using GROUP BY.

When Indexes May Hurt Performance
  1. 1

    Too many indexes slow down INSERT, UPDATE, DELETE operations.

  2. 2

    Large indexes consume significant memory and disk space.

  3. 3

    Ineffective indexes (e.g., on low-cardinality columns) do not help query speed.

  4. 4

    Using functions on indexed columns prevents index usage.

In summary, indexes are essential for speeding up data retrieval in MySQL, especially on large tables. However, they should be created thoughtfully, as unnecessary indexes can slow down write operations and waste storage.

Scenario Questions

0-2 years experience

  1. 1You have a `users` table with a million rows and need to fetch a user by email quickly. How would you add and use an index for that query?
  2. 2If you create an index on a column that contains many duplicate values, what effect might you see on INSERT speed and why?
  3. 3What difference would you expect in query execution time when searching on an indexed column versus a non‑indexed column?

2-5 years experience

  1. 1A recent feature added a composite index on (order_date, status) but the query got slower. Walk me through how you’d diagnose and decide what to do with that index.
  2. 2During a code review you spot a LEFT JOIN on a large table without an index on the join column, causing timeouts. How would you fix it and what trade‑offs would you consider?
  3. 3Explain why a covering index could help a reporting query and how you’d verify it’s being used.

5-8 years experience

  1. 1Our analytics service runs heavy aggregations on a partitioned `events` table with billions of rows. How would you design an indexing strategy that balances read latency, write throughput, and storage cost?
  2. 2We need real‑time search on a text column while maintaining high write rates. Discuss the pros and cons of a MySQL full‑text index versus a secondary B‑tree index and how you’d integrate the chosen solution.
  3. 3If we plan to migrate to a sharded MySQL architecture, what index‑related considerations must we address to avoid performance regressions?

8+ years experience

  1. 1Across several services we have legacy tables with dozens of unused indexes causing maintenance overhead. How would you lead a cross‑team effort to audit, consolidate, and evolve the indexing strategy while keeping backward compatibility?
  2. 2Design a policy for index lifecycle management in a large e‑commerce platform that experiences seasonal traffic spikes and rapid data growth. What metrics would you monitor and how would you automate index creation and removal?
  3. 3When a new microservice starts writing to a shared MySQL database, how would you coordinate index changes to avoid lock contention and downtime across the organization?

Follow-up Questions

  • How would you confirm that MySQL is actually using the index you created?
  • What monitoring or metrics would you track to see an index’s impact over time?
  • If an index improves read latency but degrades write performance, how do you decide whether to keep it?
Share

Share via WhatsApp, X, Facebook, LinkedIn or copy link. Open Graph preview enabled.