Indexes and Performance

Why Indexes Exist

An index is a separate structure that lets MySQL find rows quickly without scanning the whole table. Searching without an index is a linear scan; with a good index it is a tree lookup.

Creating an Index

CREATE INDEX idx_customers_city ON customers(city);
CREATE UNIQUE INDEX idx_customers_email ON customers(email);

Where Indexes Help

EXPLAIN SELECT * FROM customers WHERE email = 'priya@example.com';

Run the query with EXPLAIN in front. The key column tells you which index MySQL used. Add indexes on columns used in WHERE, JOIN and ORDER BY clauses.

When Indexes Hurt

Every index slows down INSERT/UPDATE/DELETE because the index must be maintained too, and it consumes disk space. Index only what your queries actually need.

Types of Indexes

• BTREE: the default, excellent for equality and range lookups.
• FULLTEXT: for text search with MATCH ... AGAINST.
• SPATIAL: for GIS and geo data.
• COMPOSITE: an index across several columns.

Key Points

• Indexes make lookups fast at the cost of write speed and disk.
• Index columns used in WHERE, JOIN and ORDER BY.
• Use EXPLAIN to see if MySQL actually uses an index.
• A composite index on (a,b) also serves queries filtering only on a.