MySQL Indexes

Indexes are used to find rows with specific column values quickly. It can execute the performance of query within fewer seconds. You can find the difference of time taken for a query to execute with and without index.

With index index it performs quickly and without index it takes some time. This is because without index the query has to scan all the rows of table ie. from first row to last row. With index it will start the search from the row where it find its column.

MySQL uses various index like,

          Primary Key

          Unique Key

          Secondary Index

          Full Text Index

Why MySQL Uses Index?

·        To find the rows matching the WHERE clause quickly.

·        To retrieve rows from other tables when performing joins.

·        To sort or group a table if the sorting or grouping is done.

·        To find the MIN() or MAX() value for a specific indexed column key_col

Primary Key

It must contain a unique value for each row of data. It cannot contain null values

There can be only one primary key for a table. We can have primary key for one or more columns.

Primary Key can be referred as a foreign key.

*   a foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table

A example for primary key,

 This example shows a table having with a primary key on two columns.

  

Unique Key

It is same as primary key but it can have null values. A table can have many unique keys. And also unique cannot be referred as a foreign key.

The below example shows a table having with a primary key and unique key.

 

Secondary Index

All indexes other than primary key are secondary indexes. Ie. We can have only one primary key in a table, if we want to create more than one index for a table we create it as secondary index mentioning index.

Syntax;

          Create index index_name on table_name (column_name);

If we don’t mention index_name mysql will automatically assign a index name based on the column.(eg : if column name is ‘student’ it will create the index as ‘student’)

We can also create secondary indexes for one or more columns.

The following example shows a table having secondary index for more than one column.

 

Full text Index

MySQL supports Full-text indexing and searching.

A full-text index in MySQL is an index of type FULLTEXT. It can be used only with MyISAM tables (until 5.5). From MySQL 5.6 and up, they can also be used with InnoDB tables.

 Full-text indexes can be created only for CHAR, VARCHAR, or TEXT columns.