MYSQL index

Introduction to index

MySQL uses indexes to quickly find rows with specific column values. Without an index, MySQL must scan the whole table to locate the relevant rows. The larger table, the slower it searches.

Creating indexes

An index is a data structure such as B-Tree that improves the speed of data retrieval on a table at the cost of additional writes and storage to maintain it.

When you create a table with a primary key or unique key, MySQL automatically creates a special index named PRIMARY. This index is called the clustered index.

The PRIMARY index is special because the index itself is stored together with the data in the same table. The clustered index enforces the order of rows in the table.

Other indexes other than the PRIMARY index are called secondary indexes or non-clustered indexes

CREATE TABLE t(
   c1 INT PRIMARY KEY,
   c2 INT NOT NULL,
   c3 INT NOT NULL,
   c4 VARCHAR(10),
   INDEX (c2,c3) 
);

CREATE INDEX index_name ON table_name (column_list)

By default, MySQL creates the B-Tree index if you don’t specify the index type. The following shows the permissible index type based on the storage engine of the table:

Storage Engine	Allowed Index Types
InnoDB	BTREE
MyISAM	BTREE
MEMORY/HEAP	HASH, BTREE

DROP INDEX `PRIMARY` ON table_name;

SHOW INDEXES FROM table_name;

Introduction to the MySQL UNIQUE index

To enforce the uniqueness value of one or more columns, you often use the PRIMARY KEY constraint. However, each table can have only one primary key. Hence, if you want to have a more than one column or a set of columns with unique values, you cannot use the primary key constraint.

Luckily, MySQL provides another kind of index called UNIQUE index that allows you to enforce the uniqueness of values in one or more columns. Unlike the PRIMARY KEY index, you can have more than one UNIQUE index per table.

To create a UNIQUE index, you use the CREATE UNIQUE INDEX statement as follows:

CREATE UNIQUE INDEX index_name

ON table_name(index_column_1,index_column_2,...);

Another way to enforce the uniqueness of value in one or more columns is to use the UNIQUE constraint.

When you create a UNIQUE constraint, MySQL creates a UNIQUE index behind the scenes.

The following statement illustrates how to create a unique constraint when you create a table.

1 2 3 4

CREATE TABLE table_name( ...    UNIQUE KEY(index_column_,index_column_2,...) );

In this statement, you can also use the UNIQUE INDEX instead of the UNIQUE KEY because they are synonyms.

Type of Indexing :

1 - Primary Indexing - ( )

2 - Clustered indexing

3 - Secondary Indexing

Indexing can be multilevel also , like we index main table data, so a virtual index table also created , if the index table is also big then you can index the index table also ,  .. this process can be repedily,   It's called the multi level indexing.

Primary Indexing : 

 - main file should be sorted ,

 - in primary indexing , only primary key is used to index

- It's also called sparx indexing( every record is not stored in index file ,while store the first entry of every block of main file ) 

 - total no of entries in index file = Total number of block in mail file , 

Clustered indexing :

- main file sorted but not by primary key attribute

- unique value stored in index table,

- 

3 - Secendery Indexing : 

- main file not sorted

- if main file is unsorted , then we can't perform the primary or clustered indexing on it.

- all the entries would be stored in index, with key or non key attributes

FULLTEXT Indexes:

Before performing a full-text search in a column of a table, you must index its data. MySQL will recreate the full-text index whenever the data of the column changes. In MySQL, the full-text index is a kind of index that has a name FULLTEXT.

CREATE TABLE table_name(

    column_list,

    ...,

    FULLTEXT (column1,column2,..)

);