Select Chapter ❯
MySQL Tutorial
MySQL Indexing
Indexing is used to speed up the whole operations occur in MySQL tables by providing a way for a database management system to go directly on a row rather than having to search all the rows until it finds the correct one you want. MySQL creates indexes for the primary keys, foreign keys, unique keys and fulltext.
To Create An Index
Syntax
CREATE INDEX index_name ON table_name ( column1, column2 ,...);
Example of Index
CREATE INDEX Student_id ON student_info (Student_name)
To Add and Drop An Index
Syntax
/*To add index*/ ALTER TABLE tableName ADD INDEX (columnlist); or /*To drop index*/ ALTER TABLE tableName DROP INDEX (columnlist);
Example of Index
/*Add index from student_id from student_info*/ ALTER TABLE student_info ADD INDEX (student_id); or /*Drop index from student_id from student_info*/ ALTER TABLE student_info DROP INDEX (student_id);
To add or drop keys
Syntax
/*To add key*/ ALTER TABLE tableName ADD keyName (columnlist); or /*To drop key*/ ALTER TABLE tableName DROP keyName (columnlist);
Example of Index
/*Add primary key from student_id from student_info*/ ALTER TABLE student_info ADD PRIMARY KEY (student_id); or /*Drop primary key from student_id from student_info*/ ALTER TABLE student_info DROP PRIMARY KEY (student_id);