MySQL Indexes

For sometime now we have been looking at MySQL database. Today we will continue to dig deep in MySQL database, but we will be discussing MySQL Indexes.

Indexes allows a MySQL database to be searched more quickly and faster. Although a MySQL database can still be searched without an index but as the database begin to grow large there will be a need for an index which will be used to identify each rows in a table and hence makes searching the table a very smooth and fast one.

Types Of Indexes

INDEX
PRIMARY KEY
FULLTEXT
Creating an Index

The way to achieve fast searches is to add an index, either when creating a table or at any time afterwards. But the decision is not so simple. You must decide which columns require an index, a judgement that requires you to predict whether you will be searching any of the data in those columns. Indexes can also get complicated, because you can combine multiple columns in one index. And even when you’ve gotten to grips with all of that, you still have the option of reducing index size by limiting the amount of each column to be indexed. You can add an index to an existing table with the command below;


ALTER TABLE staff ADD INDEX(employee(20));
1
ALTER TABLE staff ADD INDEX(employee(20));
Also you can add index while creating a table using CREATE INDEX. The two options are equivalent, except that CREATE INDEX cannot be used to create an index of type PRIMARY KEY.


CREATE TABLE staff (
employee VARCHAR(65),
position VARCHAR(65),
department VARCHAR(50),
INDEX(employee(20)),
INDEX(position(16))) ENGINE MyISAM;

CREATE TABLE staff (
employee VARCHAR(65),
position VARCHAR(65),
department VARCHAR(50),
INDEX(employee(20)),
INDEX(position(16))) ENGINE MyISAM;
Primary key

The PRIMARY KEY constraint uniquely identifies each record in a database table. Primary keys must contain unique values. A primary key column cannot contain NULL values. Most tables should have a primary key, and each table can have only ONE primary key. You can add a primary key to a table using either the commands below.


ALTER TABLE staff ADD position VARCHAR(20) PRIMARY KEY;

ALTER TABLE staff ADD position VARCHAR(20) PRIMARY KEY;
OR


CREATE TABLE staff (
employee VARCHAR(65),
position VARCHAR(65),
department VARCHAR(50),
PRIMARY KEY (position(20))) ENGINE MyISAM;

CREATE TABLE staff (
employee VARCHAR(65),
position VARCHAR(65),
department VARCHAR(50),
PRIMARY KEY (position(20))) ENGINE MyISAM;
FULLTEXT

Unlike a regular index, a FULLTEXT index in MySQL allows super-fast searches of entire columns of text. What it does is store every word in every data string in a special index that you can search using “natural language,” in a similar manner to using a search engine.

Below  are some things that you should know about FULLTEXT indexes:

FULLTEXT indexes can be used only with MyISAM tables, the type used by MySQL’s default storage engine (MySQL supports at least 10 different storage engines). If you need to convert a table to MyISAM, you can usually use the MySQL command ALTER TABLE tablename ENGINE = MyISAM; .
FULLTEXT indexes can be created for CHAR , VARCHAR , and TEXT columns only.
A FULLTEXT index definition can be given in the CREATE TABLE statement when a table is created, or added later using ALTER TABLE (or CREATE INDEX ).
For large data sets, it is much faster to load your data into a table that has no FULLTEXT index and then create the index than it is to load data into a table that has an existing FULLTEXT index.
You can create a FULLTEXT index with the command below:

MySQL

ALTER TABLE staff ADD FULLTEXT(employee,position);

ALTER TABLE staff ADD FULLTEXT(employee,position);