MySQL DatabaseLesson 59Free preview

MySQL Create Index

This lesson is part of MySQL · All levels

Summary

An index is a specialized structure that speeds up the search, sequencing, and merging of data without changing the contents of a table. Indexes are created with a CREATE INDEX or defined directly alongside constraints such as the PRIMARY KEY and UNIQUE. But indexes are not free: they use storage space and slow down the writing process (INSERT, UPDATE, DELETE).

Key points

CREATE INDEX name_index ON table (column) to create a common index
CREATE UNIQUE INDEX creates an index while preventing duplicate values
The most useful index is in the commonly used columns WHERE, JOIN, and ORDER BY
Advantages: speeding up reading; disadvantages: increasing storage and slowing down writing
Check the execution plan with EXPLAIN to see the index used

Full lesson

Code examples and quizzes

Full lessons, code examples, and quizzes are available to subscribers. 5 questions.