MySQL Index Interview Cheat Sheet: Curated Questions with In‑Depth Answers

Question 1: What index types exist in MySQL? What are their advantages and disadvantages?

Answer: MySQL mainly has the following index types:

Primary Key Index : a special unique index that cannot be NULL. Each table can have only one. Advantage: fast query speed. Disadvantage: only one per table and cannot contain NULL values.

Unique Index : similar to a primary key but a table can have multiple. Advantage: guarantees data uniqueness. Disadvantage: insert and delete operations may be slower than a table without the index.

Regular Index (Index or Key) : the most basic index with no restrictions. Advantage: can improve data query speed. Disadvantage: for large tables, creating and maintaining the index may consume considerable time and disk space.

Full‑Text Index : primarily used for text search. Advantage: enables full‑text search. Disadvantage: only works with MyISAM and InnoDB (from MySQL 5.6) storage engines, and Chinese support is limited.

Question 3: When should an index be used and when should it not be used?

Answer: Situations to use an index:

Creating an index on columns frequently used for search, sorting, or joining can greatly improve query speed.

Creating a unique index on columns with high uniqueness requirements to guarantee data uniqueness.

Situations not to use an index:

On small tables, an index may not bring noticeable performance gains and adds storage and maintenance overhead.

On columns with frequent INSERT, UPDATE, DELETE operations, because the index must be maintained dynamically, which can degrade write performance.

On columns containing a large amount of duplicate data, where the index effect is minimal.

Question 4: Can you explain the left‑most prefix principle in detail?

Answer: The left‑most prefix principle means that if a query does not start from the leftmost column of a composite index, the index will not be used. For example, with an index on (col1, col2, col3), a query that only references col2 and col3 may cause MySQL to ignore the index, whereas a query that includes col1 (or col1 and col2) can use the index. This is because MySQL stores index entries left‑to‑right; if the query does not start from the leftmost column, MySQL may need to perform a full table scan, which is usually slower. However, the optimizer may still choose to use the index depending on the specific query conditions.

Question 5: How would you optimize MySQL indexes to improve query performance?

Answer: Common strategies include:

Avoid over‑indexing : each extra index adds overhead to INSERT, UPDATE, and DELETE because MySQL must update the index. Create indexes only on columns that need query performance improvements.

Use covering indexes : if a query can be satisfied entirely from the index without accessing the data rows, MySQL can use a covering index to boost performance.

Consider column order in multi‑column indexes : place the most frequently used search, filter, or sort columns at the front of the index.

Periodically analyze and optimize tables : use ANALYZE TABLE to refresh statistics and OPTIMIZE TABLE to reorganize physical storage and indexes (effectiveness may vary by storage engine).

Monitor index usage : leverage the slow query log and Performance Schema to identify unused or poorly used indexes and adjust them accordingly.

Question 6: What is index fragmentation, how does it affect performance, and how can it be resolved?

Answer: Index fragmentation refers to unused space within an index caused by inserts, deletes, and updates. Fragmentation consumes extra disk space and can degrade query performance because MySQL must read more disk blocks.

Ways to address fragmentation:

Reorganize the index : use OPTIMIZE TABLE or specialized tools to eliminate fragmentation and improve storage utilization.

Regular maintenance : periodically clean obsolete data and reorganize tables and indexes to reduce fragmentation.

Monitor fragmentation : use MySQL‑provided or third‑party tools to track fragmentation and act promptly.

Question 7: Explain InnoDB clustered and non‑clustered indexes and their differences.

Answer: In InnoDB, a clustered index is built on the primary key. Its leaf nodes contain the actual data rows, allowing direct data access without an extra lookup. Every InnoDB table has one clustered index; if no explicit primary key is defined, InnoDB chooses a unique non‑NULL index or creates a hidden row ID.

Non‑clustered (secondary) indexes store only the indexed columns and a pointer to the primary key row. Accessing data via a non‑clustered index requires an additional lookup to fetch the full row. Multiple non‑clustered indexes can be created to support different query patterns.

The main difference is storage and access efficiency: clustered indexes store data together with the index, offering faster reads, while non‑clustered indexes need an extra step but allow many different indexing strategies.

Question 8: What common pitfalls should be considered when using composite indexes?

Answer: Common pitfalls include:

Left‑most prefix limitation : if the column order in the index does not match the order used in queries, the index may not be fully utilized.

Selectivity and cardinality : high‑selectivity columns should be placed first in a composite index; low‑cardinality columns are less effective.

Avoid functions on indexed columns : applying calculations or functions to indexed columns can render the index unusable.

Index length and type : overly long indexes increase storage and maintenance cost; very short indexes may not filter enough. Choose the appropriate index type (B‑tree, hash, etc.) based on workload.

Monitor and evaluate index usage : regularly use MySQL's slow‑query log and Performance Schema to assess which indexes are used and adjust the strategy accordingly.

Question 9: Explain MySQL spatial indexes and the scenarios where they are especially useful.

Answer: Spatial indexes handle geographic data and are supported by MyISAM and InnoDB (from MySQL 5.7.4). They are based on an R‑tree structure and enable fast retrieval of two‑dimensional spatial data such as points, lines, and polygons. Typical use cases include GIS applications, location‑based queries, and map services—for example, finding points near a given location or all points within a polygon.

Question 10: Why can a full table scan sometimes be faster than using an index?

Answer: Full scans may outperform indexes when:

Data distribution : if the queried value is very common or the table is uniformly distributed, scanning the whole table may be cheaper than traversing many index entries.

Low selectivity or lack of covering index : an index with low selectivity or one that does not cover the needed columns can cause extra I/O.

Cache effects : when the table resides entirely in memory, a full scan reads sequentially from memory, which can be faster than random index lookups.

Optimizer decision : the MySQL optimizer may choose a full scan as the cheapest execution plan based on statistics.

Actual performance depends on query conditions, data distribution, index design, and configuration, so testing and monitoring are essential.

Question 11: Explain prefix indexes and their use cases.

Answer: A prefix index indexes only the first N characters of a column, saving storage space and reducing index creation time. It is useful for large text columns (VARCHAR, TEXT) where indexing the full value would be costly. However, prefix indexes may reduce query precision because only the leading characters are considered.

Question 12: What is the difference between a unique index and a primary key?

Answer: Both enforce uniqueness, but:

Primary keys cannot be NULL, while unique indexes can allow NULL values.

A table can have only one primary key but multiple unique indexes.

Defining a primary key automatically creates a unique index; creating a unique index does not make it a primary key.

Primary keys are typically used to uniquely identify rows and serve as foreign key targets; unique indexes are used to enforce uniqueness on non‑primary columns such as usernames or email addresses.

Question 13: How do indexes affect write operations (INSERT, UPDATE, DELETE)?

Answer: Indexes impact writes in several ways:

Insert performance : each insert must also update every index on the table, adding overhead.

Update performance : updating indexed columns may require rebalancing B‑tree nodes, causing extra work.

Delete performance : deletions must remove entries from indexes, potentially leaving gaps that may need later maintenance.

Proper index design can mitigate these impacts while preserving query performance.

Question 14: Explain composite (union) indexes and when they are especially useful.

Answer: A composite index spans multiple columns, sorting and storing rows based on the combined values. It is effective when queries involve conditions on several columns, such as searching by name and age together. Composite indexes also enable covering indexes when all required columns are included, and they follow the left‑most prefix rule, so placing the most selective columns first maximizes benefit.

Question 15: What is a "back‑table" operation in MySQL and how to avoid it?

Answer: A back‑table operation occurs when a secondary (non‑clustered) index is used to locate the primary key, after which MySQL must fetch the full row from the table. This adds overhead.

Ways to avoid frequent back‑table lookups:

Use covering indexes that contain all columns needed by the query.

Avoid SELECT *; select only required columns.

Design the schema so related columns are stored together and appropriate indexes are created.

Question 16: Explain full‑text indexes and their ideal scenarios.

Answer: Full‑text indexes enable efficient keyword searches on text columns using an inverted index. They are ideal for content search in news sites, blogs, e‑commerce product descriptions, and for relevance‑based ranking of results.

Question 17: Common performance optimization suggestions when using indexes.

Answer: Recommendations include:

Prioritize columns with high selectivity when choosing index columns.

Avoid full table scans by adding or adjusting indexes, using covering indexes, or rewriting queries.

Regularly monitor index usage metrics (hit rate, scanned rows) and adjust the index set accordingly.

Design the database schema to reduce complex joins and cross‑table queries.

Consider caching frequently accessed, rarely changed data with tools like Memcached or Redis.

Question 18: What is a MySQL execution plan and how to view it?

Answer: The execution plan is the query execution strategy generated by the optimizer, describing how MySQL will retrieve data, which indexes will be used, and how many rows will be scanned. To view it, prepend EXPLAIN to the query, e.g.:

EXPLAIN SELECT * FROM users WHERE age > 25;

Question 19: Meaning of columns in EXPLAIN output.

Answer: Common columns include:

id : identifier for the query step.

select_type : type of SELECT (SIMPLE, SUBQUERY, UNION, etc.).

table : table involved.

type : join type (ALL, index, range, ref, eq_ref, const, etc.).

possible_keys : indexes that could be used.

key : index actually used.

key_len : length of the used index.

ref : columns or constants used for index lookup.

rows : estimated number of rows MySQL must examine.

Extra : additional important information.

Question 20: How to interpret the "type" column and which types are efficient?

Answer: Types from low to high efficiency:

ALL – full table scan (least efficient).

index – full index scan.

range – range scan on an index.

ref – non‑unique index lookup or prefix lookup.

eq_ref – one‑row lookup per key value (very efficient).

const, system – query part optimized to a constant (highly efficient).

Prefer "ref", "eq_ref", or "const"; avoid "ALL" and "index" when possible.

Question 21: What to do if EXPLAIN shows the expected index is not used?

Answer: Steps to troubleshoot:

Verify the index exists.

Check index selectivity; low selectivity may cause the optimizer to skip it.

Ensure query conditions match the indexed columns and avoid functions on them.

Consider using FORCE INDEX as a temporary measure.

Update statistics with ANALYZE TABLE.

Rewrite the query to make the optimizer understand the intent better.

Review MySQL configuration options such as optimizer_search_depth and optimizer_prune_level before changing them.

Question 22: Possible information in the "Extra" column and its optimization value.

Answer: Common values include:

Using where – WHERE clause applied after storage engine returns rows.

Using index – indicates a covering index is used, avoiding table row access.

Using temporary – MySQL creates a temporary table (often for ORDER BY).

Using filesort – MySQL must sort rows without index support.

Using join buffer – a join buffer is used when indexes cannot help.

These hints help identify bottlenecks such as filesort or temporary tables, guiding index adjustments.

Question 23: When should EXPLAIN ANALYZE be used instead of regular EXPLAIN?

Answer: EXPLAIN ANALYZE provides actual execution times and statistics, useful for deep performance analysis. It is common in PostgreSQL; MySQL typically relies on EXPLAIN plus profiling commands like SHOW PROFILES and SHOW STATUS to obtain runtime details.

Question 24: How to interpret the "rows" column in EXPLAIN output?

Answer: The "rows" column shows MySQL's estimate of how many rows must be examined to satisfy the query step, based on table statistics. It is a reference metric, not an exact count, and can differ from actual rows processed during execution. Use it together with other columns and real‑world testing to gauge query performance.