Tutorials › MySQL › MySQL Indexes

MySQL Indexes

5 min read Quiz at the end

Add B-Tree and FULLTEXT indexes, use EXPLAIN to verify they are used, and drop unused indexes.

Indexes

-- Create indexes
CREATE INDEX idx_email ON users(email);
CREATE UNIQUE INDEX idx_slug ON posts(slug);
CREATE INDEX idx_user_date ON orders(user_id, created_at DESC);

-- Full-text index (for MATCH AGAINST)
CREATE FULLTEXT INDEX idx_content ON posts(title, body);

-- Show indexes
SHOW INDEX FROM users;

-- Drop index
DROP INDEX idx_email ON users;

-- EXPLAIN — check if index is used
EXPLAIN SELECT * FROM users WHERE email = 'a@b.com';
-- Look for: type=ref, key=idx_email (not NULL)

-- Index types
-- B-Tree (default) — range queries, ORDER BY
-- FULLTEXT          — text search
-- HASH              — exact match only (Memory engine)

← Subqueries Next: MySQL Transactions →

Topic Quiz · 2 questions

Test your understanding before moving on

1. Which column type should be indexed for a WHERE email = query?

💡 Add an index on VARCHAR columns used in WHERE or JOIN for fast equality lookups.

2. What does EXPLAIN show in MySQL?

💡 EXPLAIN reveals the query execution plan — look for type=ALL (bad) vs type=ref (good).

Quick Access

MySQL Indexes

Indexes

Test your understanding before moving on