Indexing · Beginner – Online Test

Question 1 Basics Beginner Practice

What is a database index?

The physical order in which rows are inserted A copy of the whole table kept for backups A constraint that prevents NULL values A separate sorted structure that maps column values to row locations, so matching rows can be found without scanning the whole table

Question 2 Index structures Beginner Practice

Which data structure do InnoDB indexes use by default?

Hash table B+ tree Linked list Binary search tree (unbalanced)

Question 3 Clustered index Beginner Practice

In InnoDB, which index stores the actual table rows in its leaf pages?

None – rows are stored separately from all indexes Every UNIQUE index The most recently created index The primary key (clustered) index

Question 4 Trade-offs Beginner Practice

What is the main cost of adding an index?

SELECT queries become slower Extra storage, and slower INSERT, UPDATE and DELETE, because every index must be kept up to date Foreign keys stop working The table can no longer have NULLs

Question 5 When indexes help Beginner Practice

A users table of 10 million rows has an index on email. Which query benefits most?

SELECT * FROM users WHERE email = 'asha@x.com' SELECT * FROM users WHERE name LIKE '%asha%' SELECT * FROM users SELECT COUNT(*) FROM users

Question 6 Indexes created by constraints Beginner Practice Query executed

No CREATE INDEX was run. How many indexes does the users table have?

users

id	email	name
(no rows)

SELECT COUNT(DISTINCT INDEX_NAME) AS indexes
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users';

indexes
0

indexes
3

indexes
1

indexes
2

Question 7 Access paths Beginner Practice

What is a full table scan?

Scanning the table's structure (columns and types) Reading only the index Reading every row of the table to find the matching ones A backup of the table

Question 8 Selectivity Beginner Practice

Why is an index on a gender column (two or three values) rarely useful on its own?

gender is a reserved word Each value matches a large share of the table, so scanning the table is cheaper than many index lookups Indexes require unique values Text columns cannot be indexed

Question 9 EXPLAIN Beginner Practice

What does EXPLAIN SELECT … show?

The execution plan: the access method, the index chosen and the estimated rows for each table The query result with explanations The SQL standard behind the query Who wrote the query

Question 10 When indexes are ignored Beginner Practice

orders has an index on order_date. Why can WHERE YEAR(order_date) = 2026 not use it efficiently, and what is the fix?

Dates cannot be indexed YEAR() is not allowed in WHERE The function hides the column from the index; write WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01' Add DISTINCT

Question 11 When indexes are ignored Beginner Practice

name is indexed. Which condition can use the index as a range?

name LIKE '%sh%' LOWER(name) = 'asha' name LIKE '%sha' name LIKE 'Ash%'

Question 12 Clustered index Beginner Practice

How many clustered indexes can a table have?

One per column Two – primary and unique Any number One – rows can be physically ordered in only one way

Question 13 Composite indexes Beginner Practice

There is a composite index on (last_name, first_name). Which WHERE clause can use it?

None of them WHERE first_name = 'Asha' WHERE last_name = 'Rao' WHERE first_name LIKE 'A%'

Question 14 Unique index Beginner Practice

What does a UNIQUE index do besides speeding up lookups?

It rejects duplicate values in the indexed column(s) It makes the column the primary key It sorts the table It forbids NULL

Question 15 Composite indexes Beginner Practice Query executed

In what order are the columns of idx_city_name stored?

customers

id	city	name
(no rows)

SELECT COLUMN_NAME
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'customers' AND INDEX_NAME = 'idx_city_name'
ORDER BY SEQ_IN_INDEX;

COLUMN_NAME
city
name

COLUMN_NAME
name
city

COLUMN_NAME
id
city
name

COLUMN_NAME
city

Indexing · Beginner

Before you start