MySQL allows to create single column index and multi-column index to facilitate quick retrieval of desired data. A single-column normal index should be used if a particular column in your table will be the focus of a considerable number of your SELECT queries. Suppose an buyers profile table consists of four columns: a unique id, first name, last name and email address. You know that the majority of the searches will be specific to either the buyers ’s last name or the email address. You should create one normal index for the last name and a unique index for the email address. Take an example by creating table namely buyers which has single column indexes:
CREATE TABLE buyers(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(35) NOT NULL, last_name VARCHAR(35) NOT NULL,
email VARCHAR(55) NOT NULL UNIQUE,/* applying unique key index */
INDEX (last_name),/* applying normal index */
PRIMARY KEY(id)
);