✏️ Explanatory Question
Level: Hard — Modern indexing features that senior candidates are expected to know; each solves a specific real problem.
Scenario: Three real needs land on your desk: (1) index a case-insensitive email search without a generated column, (2) safely test whether dropping an index breaks anything before actually removing it, and (3) speed up an ORDER BY created_at DESC query. MySQL 8.0 has a dedicated feature for each. What are they?
MySQL 8.0.13+ lets you index an expression directly, without creating a separate generated column. Internally it creates a hidden virtual column, but you write it inline in the index definition.
-- Index the LOWERCASE of email for case-insensitive lookups
CREATE INDEX idx_email_lower ON users ( (LOWER(email)) );
-- Note the extra parentheses around the expression
-- This query now uses the functional index
SELECT * FROM users WHERE LOWER(email) = 'rumman@example.com';
-- Index a date extracted from a datetime
CREATE INDEX idx_order_day ON orders ( (DATE(created_at)) );
SELECT * FROM orders WHERE DATE(created_at) = '2026-08-02'; -- uses indexFunctional index vs generated column: Both make an expression indexable. A functional index is more concise (no visible extra column) and ideal when you only need the expression for indexing. Use a generated column when you also want to select or reference the computed value elsewhere.
An invisible index is maintained by MySQL but ignored by the optimizer. It lets you simulate "what if this index did not exist?" safely, without the risk and cost of actually dropping and recreating it.
-- Make an index invisible (optimizer ignores it, but it's still maintained)
ALTER TABLE users ALTER INDEX idx_old_index INVISIBLE;
-- Run your queries / monitor production. If nothing breaks or slows down,
-- it's safe to drop. If performance tanks, instantly bring it back:
ALTER TABLE users ALTER INDEX idx_old_index VISIBLE;
-- Once confirmed unused, drop it for real
ALTER TABLE users DROP INDEX idx_old_index;
-- See which indexes are invisible
SELECT INDEX_NAME, IS_VISIBLE
FROM information_schema.STATISTICS
WHERE TABLE_NAME = 'users';Why this matters: Dropping an index on a huge table is expensive and risky — if it turns out to be needed, rebuilding it takes hours. Making it invisible first gives you a safe, instant, reversible test before committing to the drop.
Before 8.0, indexes were ascending and MySQL just scanned them backward for DESC. MySQL 8.0 supports truly descending indexes, which help mixed-direction sorts.
-- A descending index (or mixed direction) for common sort patterns
CREATE INDEX idx_created_desc ON orders (created_at DESC);
-- Especially useful for MIXED-direction ORDER BY
CREATE INDEX idx_dept_salary ON employees (dept_id ASC, salary DESC);
-- This ORDER BY now matches the index exactly (no filesort)
SELECT * FROM employees
WHERE dept_id = 10
ORDER BY salary DESC;| Feature | Solves | Since |
|---|---|---|
| Functional index | Index an expression (LOWER, DATE) directly | 8.0.13 |
| Invisible index | Safely test dropping an index | 8.0.0 |
| Descending index | Efficient DESC / mixed-direction sorts | 8.0.0 |
Bonus — invisible indexes for primary keys: You cannot make a primary key invisible. Also, a query with an explicit index hint (FORCE INDEX) can still be blocked if that index is invisible — the optimizer truly pretends it does not exist unless you set use_invisible_indexes=on in the optimizer switch for testing.
Interviewer follow-up: "How do you find unused indexes worth making invisible?" → Query performance_schema / sys.schema_unused_indexes, which lists indexes with zero reads since the last server restart. Confirm over a full business cycle (a week covers weekly jobs), make the candidate invisible, monitor, then drop — a safe, data-driven index-cleanup workflow.