MySQL Indexing

Indexing

It is a data structure that improves the speed of search in database. We add the indexing on columns of table. We can one or more indexing on table.

Advantage of Indexing
It improve the speed of search. If we add indexing on any column, It will search faster.

Disdvantage of Indexing

INSERT and UPDATE statements take little more time on tables.

Following are different types of MySQL indexes

Column Index: In this type, we add indexing on single column

ALTER TABLE table_name ADD INDEX (field_name);

Concatenated Index: In this type, we add indexing on two OR more columns.

ALTER TABLE table_name ADD INDEX (field_name1, field_name2);

Use only when you are using query in below way

SELECT * FROM table_name  WHERE field_name1='text1' AND field_name2='text2';

Partial Index : In this type, we add indexing on single columan and on few number of character like first 10 character.

ALTER TABLE table_name ADD INDEX (field_name(10));

Following are some examples of indexing

create a table cities 

CREATE TABLE IF NOT EXISTS `cities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Add Indexing

alter table `cities` add index name (name)

List all the Indexing in cities table

show index from `cities`

Drop Indexing

alter table `cities` drop index name