Indexing · Advanced

A B+ tree has a fan-out of 100 (every node has 100 children or leaf entries). What is the minimum number of levels needed to index 1,000,000 keys?

A disk block is 1024 bytes, a search key is 10 bytes and a block pointer is 6 bytes. What is the maximum order p (number of child pointers) of a B+ tree internal node?

Why do databases prefer B+ trees over B trees for indexes?

What is the difference between a dense and a sparse index?

A file of 30,000 records is sorted on its key, with 10 records per block. How many entries does a sparse primary index on that key need?

That sparse index (3,000 entries) is stored with 100 entries per block and searched with binary search. How many block accesses are needed to find a record, including reading the data block?

The hot query is WHERE status = 'paid' AND created_at >= ? ORDER BY created_at. Which composite index is best?

The app must search emails case-insensitively with WHERE LOWER(email) = ?, and the column's collation cannot change. How do you make it indexable?

Users complain the site is slow. What is a sound first step for finding database problems?

Why is SELECT COUNT(*) FROM orders slow on a huge InnoDB table, while it is instant on MyISAM?

A logs table grows by 50 million rows a month, and data older than 90 days must be deleted every day. Deleting by date is slow and locks the table. What helps most?

With an index on (a, b), which query can return rows in b order without an extra sort?

A junior developer suggests indexing every column "to be safe". Which is the strongest argument against it?

Why do sequential primary keys (AUTO_INCREMENT) insert faster than random ones in InnoDB?

Before dropping an index that looks unused, what is a safe practice in MySQL 8?