Katkat
Back

Explain Composite and Covering Indexes in MySQL

ByteDanceSoftware Engineer · Technical Screen
MediumSoftware EngineerTechnical ScreenSoftware Engineering Fundamentals

Last updated

This question tests the understanding of MySQL B-tree index structures, specifically how composite index ordering affects query performance, selectivity, and disk I/O.

The Scenario

Suppose you manage a table orders in a high-traffic MySQL (InnoDB) database with the following schema:

CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    tenant_id INT NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    amount DECIMAL(10, 2),
    INDEX idx_tenant_status_created (tenant_id, status, created_at)
);

The Task

Explain how the composite index idx_tenant_status_created optimizes the following query scenarios. For each, describe whether the index is fully utilized, partially utilized, or ignored, and explain the role of the "Leftmost Prefix" rule and the "Clustered Index" lookup.

  1. SELECT * FROM orders WHERE tenant_id = 10 AND status = 'PENDING' ORDER BY created_at DESC;
  2. SELECT * FROM orders WHERE tenant_id = 10 AND created_at > '2023-01-01';
  3. SELECT tenant_id, status, created_at FROM orders WHERE tenant_id = 10;

Requirements

  • Explain why the order of columns in the index matters for range queries.
  • Contrast the performance of Scenario 3 (Covering Index) with a query that selects the amount column.
  • Discuss the "Index Condition Pushdown" (ICP) optimization if applicable.

Hints

Hint 1

Recall that B-tree indexes are stored as sorted sequences; the index is only searchable by a prefix of the defined column order.

Hint 2

A 'covering index' occurs when all requested columns exist within the index B-tree nodes, eliminating the need for a secondary 'bookmark' lookup into the clustered index (the primary key structure).

Hint 3

Range predicates on a column (e.g., '>') effectively stop the index from being used for further filtering or sorting in that specific index structure, though they can still be used for initial narrowing.

Follow-up questions

  • If we frequently run a query filtering only by `status`, how would you optimize the index strategy without creating redundant indexes?
  • How does the size of the index (in bytes) impact the likelihood of it fitting into the InnoDB Buffer Pool compared to the full table row data?
  • What are the trade-offs of adding more columns to an existing index vs. creating a new, separate index?