All TalkersCode Topics

Follow TalkersCode On Social Media

devloprr.com - A Social Media Network for developers Join Now ➔

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);
❮ PrevNext ❯