Do you want to be a good Web Developer?

Enter Your Email To Get Our Exclusive Tutorials
And Offer Right In Your Email

MySQL Indexing

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

Join Us With Our 21000+ Subscribers And Get Our Latest Tutorials Update Via Email