How to create and utilize indexes in MySQL
There are two ways to create a MySQL index:
- Creating an index using the CREATE INDEX statement.
CREATE INDEX index_name ON table_name (column_name);
For example, to create an index named student_id on a table called students:
CREATE INDEX idx_student_id ON students (student_id);
- Add an index using the ALTER TABLE statement.
ALTER TABLE table_name ADD INDEX index_name (column_name);
For example, to add an index named student_id on a table called students.
ALTER TABLE students ADD INDEX idx_student_id (student_id);
The syntax for using MySQL indexes is as follows:
SELECT columns FROM table_name WHERE condition;
For example, if you want to select the student named John from a table named students:
SELECT * FROM students WHERE name = 'John';
By creating and using indexes, the performance and efficiency of database queries can be greatly improved.