Explain Composite and Covering Indexes in MySQL

A MySQL table has a composite B-tree index on `(tenant_id, status, created_at)`. Explain the leftmost-prefix rule, which query shapes can use the index efficiently, and why a covering index can be faster than an index that still requires table lookups. ### Constraints & Assumptions - Discuss equality predicates, range predicates, ordering, and selected columns separately. - Do not reduce the answer to the slogan that a query either uses or does not use an index. - Assume an InnoDB-style clustered primary key unless you state another storage model. ### Clarifying Questions to Ask - Which columns are selected and in what order must rows be returned? - How selective are the predicates and how many rows are expected? - Does the table use the primary key as the clustered record location? ### What a Strong Answer Covers - How lexicographic B-tree order enables prefixes beginning with `tenant_id`. - Why skipping the leading column usually prevents a direct contiguous seek. - How a range on one key part limits use of later parts for navigation or ordering. - How a covering secondary index supplies all requested values without fetching clustered rows. - Write amplification, index width, cache residency, and validation with an execution plan. ### Follow-up Questions - Would the index serve `WHERE tenant_id = ? ORDER BY created_at` when status is absent? - When can an optimizer still scan or skip-scan despite a missing leading predicate? - How would you choose between two narrow indexes and one wide covering index?