MySQL Index

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.

Indexes are a type of lookup table that is used to improve the performance of SELECT queries. Without an index, MySQL performs lookups by sequential searching the table. It has to start with the first record and then read the whole table to find the relevant rows. Conversely, if the table has an index for the columns in question, MySQL can quickly find the data without having to look at all the rows in a table.

The query optimizer may use indexes to quickly locate data without having to scan every row in a table for a given query.

Indexes can consist of one or more fields up to a total of 16 columns. For certain data types, you can even index a prefix of the column. One way to think about multiple-column indexes is like a sorted array containing values that are created by concatenating the values of the indexed columns. For this reason, indexes on multiple fields are also known as “concatenated indexes”.

CREATE INDEX cannot be used to create a PRIMARY KEY.

Syntax to create an index.

CREATE INDEX [index name] ON [table name]([column name]);

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

CREATE INDEX emailIndex ON user(email);

EXPLAIN SELECT * FROM play_ground.user WHERE email = 'lau@gmail.com';

Multiple column index

A multiple-column index is also know as composite index.

Syntax to create a multiple-column index

CREATE INDEX [index name] ON [table name]([column1,column2,column3,...]);

The query optimizer cannot use the index to perform lookups if the columns do not form a leftmost prefix of the index. In the where clause, all columns in the multiple index must be used otherwise the index will not be used.

CREATE INDEX complexIndex ON user(name,email);

SELECT * FROM user
WHERE name = 'Lau' AND email = 'lau@gmail.com';

The above query works because in the WHERE clause, name and email are both used.

SELECT * FROM user
WHERE email = 'lau@gmail.com';

The above query does not use our complexIndex as it does not have both of the composite index columns(name and email).

---