Sample questions
Mysql IndexesDifficulty 1
InnoDB indexes are organized as B+-tree structures. What is actually stored in the leaf nodes of an InnoDB B+-tree index?
- aThe full row data (for a clustered index) or a reference to the clustered key (for a secondary index) — the leaf nodes carry the real payload✓
- bOnly pointers to fixed disk-block addresses, ordered by insertion time rather than key value
- cA hash table mapping each key value directly to a row offset
- dCompressed summaries of each page, with the actual data kept only in the root node
Explanation:InnoDB's B+-tree keeps navigation keys in the internal nodes and the actual payload in the leaves: for the clustered index (primary key), the leaf holds every column of the row; for a secondary index, the leaf holds the indexed column plus the primary key value used to look the row up. Internal/root nodes only route the search, they don't hold row data, which rules out (b), (c) and (d).
Mysql IndexesDifficulty 1
In InnoDB, what does it mean that a table's primary key is a 'clustered index'?
- aPrimary key values are cached separately in memory for fast lookup, with no relation to how rows are physically stored
- bThe table's actual row data lives directly inside the primary key's B+-tree leaf pages, physically ordered by the primary key✓
- cThe primary key column must simultaneously be covered by more than one index
- dRows are grouped into fixed-size partitions based on a hash of the primary key
Explanation:'Clustered index' means the table data itself IS the index structure: rows are stored in primary-key order inside the B+-tree leaf pages, so there is no separate row storage to point to. This is the core structural difference from a table with only secondary indexes, which merely point at this clustered data. (a), (c) and (d) all describe mechanisms InnoDB doesn't use for this.
Mysql IndexesDifficulty 2
A secondary index exists on users.email, and the table's primary key is id. What does a leaf-node entry of that secondary index actually contain?
- aA direct pointer to the physical page/offset where the row is stored, kept updated whenever the row moves
- bA full copy of every column in the matching row, kept in sync with the clustered index
- cThe indexed
email value plus the row's primary key value✓ - dOnly the
email value; a secondary index alone can never be used to retrieve the rest of the row
Explanation:InnoDB secondary index leaves store the indexed column(s) plus the row's primary key value(s), not a physical pointer. To fetch other columns, InnoDB does a second lookup into the clustered index using that primary key — this indirection is exactly why InnoDB's PK choice matters so much for secondary-index performance. (a) describes a physical-pointer model InnoDB doesn't use, (b) and (d) misdescribe what's stored and what's retrievable.
Mysql IndexesDifficulty 1
A table is created without any PRIMARY KEY and without any UNIQUE NOT NULL index. What does InnoDB use internally as the clustered index in this case?
- aIt refuses to create the table at all until an explicit primary key is added
- bIt silently falls back to MyISAM-style heap storage for just that table
- cIt uses whatever column is defined first in
CREATE TABLE, regardless of its type - dIt generates a hidden, internal
ROW_ID column and clusters the table on that✓
Explanation:InnoDB requires every table to have a clustered index. If you declare a PRIMARY KEY, that's used; otherwise it picks the first UNIQUE NOT NULL index; if neither exists, InnoDB silently adds an invisible auto-incrementing ROW_ID column and clusters on it. The table is still created (rules out a), it stays InnoDB (rules out b), and column order/type play no role (rules out c).
Mysql IndexesDifficulty 2
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
returns
possible_keys: idx_customer_id,PRIMARY and
key: idx_customer_id. What's the difference between the
key column and the
possible_keys column here?
- a
key is the single index the optimizer actually chose to execute this query; possible_keys only lists candidate indexes it considered✓ - b
key lists every index that exists on the table; possible_keys lists ones scheduled to be used in a future query - c
key and possible_keys are always identical; MySQL just repeats the information for older client compatibility - d
key shows only the primary key index; possible_keys shows only secondary indexes, regardless of relevance
Explanation:possible_keys is the optimizer's shortlist of indexes that could satisfy the query's predicates; key is the one it actually decided to use after cost estimation. Here both idx_customer_id and PRIMARY were viable, but only idx_customer_id was chosen. (b), (c), (d) all misdescribe the relationship between the two columns.
Mysql IndexesDifficulty 2
A query uses a composite index (customer_id, order_date) where customer_id is an INT (4 bytes). EXPLAIN shows key_len: 4. What does this value tell you?
- aThe index contains exactly 4 matching entries for this query
- bOnly the 4-byte
customer_id key part is used for this lookup✓ - cThe full composite index, spanning 4 columns in total, is being used
- dThe index scan touched exactly 4 index pages before it returned
Explanation:key_len reports how many bytes of the index key were actually used to satisfy the query, which is a direct signal of how many leading columns of a composite index participated in the lookup. key_len: 4 matching exactly the size of customer_id means the query only used that leading column — order_date wasn't part of the range/equality condition applied via the index. It has nothing to do with row counts (a), page counts (d), or total column count (c).