Home / Indexes / Question No: 139

I am newbie to MySQL and learning about indexes terminology.  Can you explain that what is partial-column and prefixed-column index or key in MySQL?

An  index is  used if a particular column in your  table will be the focus of a considerable number of your SELECT  queries. For example, suppose an buyer 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 two indexes for these columns: one normal index for the last_name and a unique index for the email address, like so:

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,
 INDEX (last_name),
 PRIMARY KEY(id)
);

To beautify the above idea and make it more useful, MySQL allows to create indexes on columns partially taking their specific length of characters, based on the idea that the first N characters of a given column often are enough to ensure uniqueness, where N is specified within the index creation parameters.  These type of indexes are also known as "partial-column indexes" or "Leftmost Prefixing".  Creating partial-column indexes requires less disk space and is considerably faster than indexing the entire column. Taking again the above example, you can imagine that using the first seven characters of the last_name suffices to ensure accurate retrieval:

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,
 INDEX ( last_name(5) ),
 PRIMARY KEY(id)
);