Indexes are needed to improve the performance of SELECT queries. Indexes in Mysql are just like the indexes available in books. Without indexes if we have to look for a topic in a book then we will have to go through each page content but luckily we have an index, we find the page number from the index and then open that page directly. Similarly in MySql if we have to find row(s) have a particular column value then without index on that column MySql will have to scan each row of the table to find the matching rows.

It is highly recommended that you create indices on columns of a table from which you often query the data.

The  most common types of indexes are the ‘Column Indexes‘ indexes – indexes created on a single column.

You can also create an index that uses only the first ‘N’ characters of the column – ‘Prefix Indexes‘. When you index a BLOB or TEXT column, you must specify a prefix length for the index.  

MySql also allows you to create composite indexes – indexes on multiple columns (‘Multi-Column Indexes‘).

Why not create indexes for all columns if indexing speeds up querying data?

• Unnecessary indexes waste space and waste time for MySQL to determine which indexes to use.
• Indexes also add to the cost of inserts, updates, and deletes because each index must be updated.

Therefore you should check whether all your queries use the indexes you have created in the tables using the “EXPLAIN” statement.

Terminologies

Key or Index

KEY is normally a synonym for INDEX. It refers to a normal non-unique index. The index may contain rows with identical values in all columns of the index. The index does not impose any constraint on your data but is just to speed up querying your data.

Unique Index

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.

Primary Key

A primary key is a column or set of columns in a table that can uniquely identify every row in a table. A primary key cannot contain NULL values, this what differentiates it from a Unique key. Mysql automatically creates an index for a primary key.

Database normalization requires that each table should have a primary key. In case a table does not have an obvious column or set of columns to use as a primary key, you might create a separate column with auto-increment values to use as the primary key.

Also a table can have only one primary key.

Foreign Key

One of the stages in designing a normalized database is to identify data that is duplicated, separate that data into a new table, and set up a foreign key relationship so that the multiple tables can be queried like a single table, using a join operation.

The foreign key relationship is supported by InnoDB tables.It is defined on one column in both the parent table and the child table. The foreign key in the child table serves as a pointer to a row in the parent table.

In addition to enabling fast lookup of related information, foreign keys help to enforce referential integrity, by preventing any of these pointers from becoming invalid as data is inserted, updated, and deleted. This enforcement mechanism is a type of constraint. A row that points to another table cannot be inserted if the associated foreign key value does not exist in the other table. If a row is deleted or its foreign key value changed, and rows in another table point to that foreign key value, the foreign key can be set up to prevent the deletion, cause the corresponding column values in the other table to become null, or automatically delete the corresponding rows in the other table.

FULLTEXT Index

It is a more specialized form of indexing that allows full text search. Think of it as (essentially) creating an “index” for each “word” in the specified column. Full-text indexes can be used only with InnoDB or MyISAM tables, and can be created only for CHAR, VARCHAR, or TEXT columns.

Spatial Indexes

MySql supports spatial data types like ‘GEOMETRY’, ‘POLYGON’ etc thus allowing you to store  geo-coding data.You can create indexes on spatial data types

using the SPATIAL keyword and syntax similar to that for creating regular indexes. Columns in spatial  indexes must be declared NOT NULL.

Points to note:

• You can use the EXPLAIN statement to check which indexes are being used by your queries.
• The maximum number of indexes per table and the maximum index length is defined per storage engine.
• All storage engines support at least 16 indexes per table and a total index length of at least 256 bytes.