MySQL Indexes

An index is like the index at the back of a book. Without it, MySQL must read every row of a table to find a value (a full table scan). With an index on the column, it finds the rows much faster.

CREATE INDEX idx_city ON students (city);

Now queries with WHERE city = ... or ORDER BY city can use the index.

---

Unique index

A unique index is also a rule: no two rows can have the same value.

CREATE UNIQUE INDEX idx_email ON users (email);

---

Index on more than one column

CREATE INDEX idx_city_marks ON students (city, marks);

This helps queries that filter by city, or by city and marks. Put the column you filter by most first.

---

See and drop indexes

SHOW INDEX FROM students;

DROP INDEX idx_city_marks ON students;
DROP INDEX idx_city ON students;

---

Check if a query uses an index

EXPLAIN SELECT * FROM students WHERE city = 'Delhi';

EXPLAIN shows how MySQL will run the query. In the column key you see the index name that is used.

---

When to use indexes

• Columns used often in WHERE, JOIN and ORDER BY.
• Do not index every column. Each index uses space and makes INSERT and UPDATE a little slower.
• Tiny tables do not need indexes.
• The PRIMARY KEY and UNIQUE columns already have an index.

---

Summary

• An index makes searches faster, but slows writes a little.
• CREATE INDEX name ON table (column); and DROP INDEX name ON table;
• Next: Views.

• Previous Page
• Next Page

---

Exercises & Assignments
No Content Found.

Interview Questions & Answers
No Content Found.