Index in MySQL


Share

A database index is a data structure that improves the speed of operations in a table. Indexes can be created using one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records.

While creating index, it should be taken into consideration which all columns will be used to make SQL queries and create one or more indexes on those columns .

Practically, indexes are also a type of tables, which keep primary key or index field and a pointer to each record into the actual table.

The INSERT and UPDATE statements take more time on tables having indexes, whereas the SELECT statements become fast on those tables. The reason is that while doing insert or update, a database needs to insert or update the index values as well.

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. For example, there are different index types, such as a regular INDEX, PRIMARY KEY, and FULLTEXT.

An alternative to using ALTER TABLE to add an index is to use the CREATE INDEX command. The two options are equivalent, except that CREATE INDEX cannot be used to create an index of type PRIMARY KEY.

ALTER TABLE classics ADD INDEX(author(20));
CREATE INDEX author ON classics (author(20));


Example:-

CREATE TABLE classics (
 author VARCHAR(128),
 title VARCHAR(128),
 category VARCHAR(16), 
 year SMALLINT,
 INDEX(author(20)),
 INDEX(title(20)), 
 INDEX(category(4)),
 INDEX(year)) ENGINE MyISAM;
 

Unique Index

You can create a unique index on a table. A unique index means that two rows cannot have the same index value. Here is the syntax to create an Index on a table.
CREATE UNIQUE INDEX index_name ON table_name ( col1, col2,...coln);

Example:-

CREATE UNIQUE INDEX students ON class (name varchar2(20), age number(2));

Displaying INDEX Information

You can use the SHOW INDEX command to list out all the indexes associated with a table. The vertical-format output (specified by \G) often is useful with this statement, to avoid a long line wraparound −
Example:-

SHOW INDEX FROM students;

Primary keys

Primary keys represents a unique values foe a each rows in a tables.
ALTER TABLE classics ADD isbn CHAR(13) PRIMARY KEY;


Example:-

ALTER TABLE classics ADD isbn CHAR(13);
UPDATE classics SET isbn='9781598184891' WHERE year='1876';
UPDATE classics SET isbn='9780582506206' WHERE year='1811';
UPDATE classics SET isbn='9780517123201' WHERE year='1856'; 
ALTER TABLE classics ADD PRIMARY KEY(isbn);
DESCRIBE classics;

Creating a FULLTEXT index

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.


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

1. 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;.

2. FULLTEXT indexes can be created for CHAR, VARCHAR, and TEXT columns only

3. 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).

4. 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.


Example:-

ALTER TABLE classics ADD FULLTEXT(author,title);