✏️ Explanatory Question
Level: Advanced — Indexing is the single most important topic for query performance.
An index is a special data structure (usually a B-tree) that stores a sorted copy of one or more columns, allowing MySQL to find rows quickly without scanning the entire table. It works exactly like the index at the back of a book — instead of reading every page, you jump straight to the right one.
A full table scan checks every row, giving linear time complexity:
$$ O(n) \text{ (full table scan)} \quad \text{vs} \quad O(\log n) \text{ (B-tree index lookup)} $$
For a table with 1,000,000 rows, a scan may check a million rows, while a B-tree index needs only about 20 comparisons — a massive difference.
WHERE, JOIN, ORDER BY, and GROUP BY.| Aspect | Without Index | With Index |
|---|---|---|
| Lookup method | Full table scan | Direct B-tree seek |
| Read speed | Slow on large tables | Very fast |
| Write speed | Faster | Slightly slower |
| Storage | Less | More (index overhead) |
-- Create an index on a frequently searched column
CREATE INDEX idx_lastname ON employees(last_name);
-- Composite index on multiple columns
CREATE INDEX idx_dept_salary ON employees(dept_id, salary);
-- View existing indexes on a table
SHOW INDEX FROM employees;
-- Check whether a query uses an index
EXPLAIN SELECT * FROM employees WHERE last_name = 'Ansari';
-- Drop an index
DROP INDEX idx_lastname ON employees;