Indexes in MySQL Tutorial

What are Indexes in MySQL

Indexes in MySQL
Indexes in MySQL

Indexes in MySQL

Databases have really eased out so much for us – storing bulk of data records, updating them, manipulating them, querying them and what not.

But you wondered when you look for something in a table, how does DBMS do it for you wondered when you look for something in a table, how does DBMS do it for you ? No idea ? No, you certainly have an idea.

Haven’t you used table of contents or indexes in your books to find or reach to specific topic directly ? The index gives you the location of the topic and you can straightway access that topic from that location.


For example, look at the sample table of contents given below.

Indexes in MySQL
Indexes in MySQL


Now to go to topic ‘Electric Potential Energy’, you can straightway open page numbered as 2.16 (as per above sample table of contents).

You need not turn over page to page to reach at this topic. Hasn’t it saved your time you would have wasted otherwise in flipping the pages to reach this topic.

In the same manner, databases create and store indexes to store the location of records. The above shown index has contents indexed topic wise.

You can decide the indexing fields in the database and the database will create and index based on the chosen index field.

For example, you can index same table on multiple different fields. Database will create and store that many different index tables as per the order of those index fields.


To understand this, consider the following example :


Suppose you have a table namely student in your database with three fields in it : Roll No., Name and Percentage marks.
⦁ If you create index on field Roll No., then the database will create an index table that will have the locations of the data records of table student in the order of index field Roll No.
⦁ If you create index on field Name, then the database will create an index that will have the locations of the data records of table student in the order of index field Name.
⦁ If you create index on field Percentage, then the database will create an index that will have the locations of the data records of table student in the order of index field Percentage.

Following Image shows you the sample table student with three different indexes created on its three different fields. Notice that an index file stores information as per the order of index field’s values.

Multiples indexes of single data table
Multiples indexes of single data table

Thus you can say that an index is a data structure be a database that helps it find records within a table more quickly. An index stored/ordered values within the index field and their location in the actual table.

Indexes can be created on one or more columns of a table. Each index file will be stored separately.


So an index in a database is also a table (a database structure) which stores arranged values of one or more columns in a specific order.

A database index first sorts the data on the basis of index-field and then it assigns an identification for each row.

The index table has only two columns, one is a row-id or rowid (that points to actual row in the table) and another is indexed-column (ordered).


Effective indexes are one of the best ways to improve performance in a database application This is because when you search or retrieve from a database table, based on the indexed column, the index pointer searches the rowid and quickly locates that position in the actual table and displays the connected row(s).

This makes the process very fast in comparison to non-index retrieval.
Let us see how you can create indexes in MySQL

Creating Indexes in MySQL

MySQL also provides commands to create indexes in a database. You can create indexes in MySQL using following commands. MySQL provides two ways to create indexes :

(i) Create index at the time of table creation i.e., along with CREATE TABLE command.
(ii) Create index on an already existing table.

(i) Create index at Time of Table Creation (i.e., with CREATE TABLE)

To create index on a field at the time of table creation, you can specify the index-fields with INDEX clause of CREATE TABLE command, whose syntax is given below :

CREATE TABLE table_name
(
      column1 datatype [NULL/NOT NULL ],
      column2 datatype [NULL/NOT NULL ],
      ...
      column_n datatype [NULL/NOT NULL ],
      ...
      INDEX index_name (index_col1 [(length)] [ASC/DESC]),
                  index_col2 [(length)] [ASC/DESC],
      ...
                   index_col_n [(length)] [ASC/DESC],
) ;

Please note that a CREATE TABLE statement with INDEX clause creates both the table as well as the index at the same time. Consider following example :

CREATE TABLE PLAYERS
(  PLAYERNO INTEGER NOT NULL,
   NAME CHAR(15) NOT NULL,
   DOB DATE,
   SEX CHAR(1) NOT NULL,
   ADDDRESS VARCHAR(100) NOT NULL,
   PHONE CHAR(15),
   TEAMNO CHAR(4) NOTNULL,
   PRMARY KEY (PLAYERNO),
   INDEX Player_idx (TEAMNO)
) ;

The above SQL statement will create a table by the name players and at the same time it will also create an index namely Player_idx on the basis of field TeamNo.

Since, we have not specified any order ASC or DESC, it will take default order ASC i.e., ascending for the index field.
Consider another example command :

CREATE TABLE PLAYERS
(  PLAYERNO INTEGER NOT NULL,
   NAME CHAR(15) NOT NULL,
   DOB DATE,
   SEX CHAR(1) NOT NULL,
   ADDDRESS VARCHAR(100) NOT NULL,
   PHONE CHAR(15),
   TEAMNO CHAR(4) NOTNULL,
   PRMARY KEY (PLAYERNO),
   INDEX Player_idx2 (Name(5))
) ;

The above command will create index namely Player_idx2 on the basis of first 5 character of field NAME, i.e., two names will be compared for indexing purpose using first 5 character also e.g., names Charu and Charulata will be treated as per this condition as their first 5 characters are same here.

This type of index is usually useful when we know that the field values are different in first n characters.


(ii) Create index on an Already Existing Table


Another way of creating index is to create it for already existing tables using following command :

CREATE INDEX <index_name> ON <table_name> (<column>[ASC/DESC],
                  <column>[ASC/DESC] ...) ;

To enforce unique values, add the UNIQUE keyword :

CREATE UNIQUE INDEX <index_name> ON <table_name> (<column>[ASC/DESC],
                  <column>[ASC/DESC] ...) ;

For example,

CREATE UNIQUE INDEX Players_idx
ON Players (TeamNo) ;

The above statement will create a Unique index on the basis of field TeamNo on an existing table namely players.

You know that the role of UNIQUE is to ensure that no duplicate entries are entered in the field.
To view indexes that have been created on a table, use command :

SHOW INDEXES FROM table_name ;

For example, command :

SHOW INDEXES FROM Players ;

will show all index that are existing for table Players.

DROP INDEX

You can remove an index using command :

DROP INDEX <indexname> ON <Table_name> ;

For example, to remove index Players_idx, write command as :

DROP INDEX Players_idx ON Players ;

Rename an Index in MySQL

You can also rename an index using command :

ALTER TABLE table_name
RENAME INDEX index_name TO new_index_name ;

For instance, following command :

ALTER TABLE Players 
RENAME INDEX Players_idx TO Players_new_idx ;

will change the name of index Players_idx to Players_new_idx for table Players.


Advantages and Disadvantages of Indexes


Although indexes improve the search and increase a database’s performance, yet it is a fact that each index is stored separately and takes up; storage space on disk. Let us discuss advantages and disadvantages of indexes.


The advantages of indexes are as follows :

(i) With indexes, queries give much better performance.
(ii) Data retrieval is much faster with indexes.
(iii) Indexes are very useful for sorting purposes.
(iv) Unique indexes guarantee uniquely identifiable records in the database.


The disadvantages of indexes are as follows :

(i) With indexes, the performance on inserts, updates, and deletes decreases. As every time insert/update/delete operation happens, the index is to be updated accordingly. And with larger number of records, it takes up lot of time.

(ii) Indexes consume storage space and this (space consumption) increases with the number of fields used and sthe length of the fields.
Thus, it is advised that one should only create indexes only when actually needed.

Read More

MySQL Create Table Statement

MySQL Commands with Examples

MySQL ORDER BY with Examples

MySQL GROUP BY Clause with Examples

Indexes in MySQL

Indexes in MySQL Video

Indexes in MySQL Video Credit :
Steve Griffith

Frequently Asked Questions

related Indexes in MySQL Tutorial

How many types of indexes are there?

two

There are two main index types: Clustered index and Non-Clustered index. A clustered index alters the way that the rows are physically stored. When you create a clustered index on a column (or a number of columns), the SQL server sorts the table’s rows by that column(s).
Indexes in MySQL Tutorial

What is an index in a database?

Indexes are a powerful tool used in the background of a database to speed up querying. Indexes power queries by providing a method to quickly lookup the requested data. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.
Indexes in MySQL Tutorial

What is the difference between key and index in MySQL?

There’s no difference. They are synonyms. From the CREATE TABLE manual entry: KEY is normally a synonym for INDEX .
Indexes in MySQL Tutorial

How many indexes can be created on a table?

SQL Server allows us to create multiple Non-clustered indexes, up to 999 Non-clustered indexes, on each table, with index IDs values assigned to each index starting from 2 for each partition used by the index, as you can find in the sys. partitions table.
Indexes in MySQL Tutorial

Is clustered index faster than nonclustered?

If you want to select only the index value that is used to create and indexnon-clustered indexes are faster. On the other hand, with clustered indexes since all the records are already sorted, the SELECT operation is faster if the data is being selected from columns other than the column with clustered index.

How do you create an index?

Create the index
Click where you want to add the index.
On the References tab, in the Index group, click Insert Index.
In the Index dialog box, you can choose the format for text entries, page numbers, tabs, and leader characters.
You can change the overall look of the index by choosing from the Formats dropdown menu.

Why indexing is used in database?

Why Indexing is used in database? Answer: An index is a schema object that contains an entry for each value that appears in the indexed column(s) of the table or cluster and provides direct, fast access to rows. The users cannot see the indexes, they are just used to speed up searches/queries.
Indexes in MySQL Tutorial

What is the purpose of indexing?

Indexing is a data structure technique which allows you to quickly retrieve records from a database file. An Index is a small table having only two columns. The first column comprises a copy of the primary or candidate key of a table.
Indexes in MySQL Tutorial

How many types of indexes are there in MySQL?

MySQL has three types of indexesINDEX, UNIQUE (which requires each row to have a unique value), and PRIMARY KEY (which is just a particular UNIQUE index).
Indexes in MySQL Tutorial

How many types of indexes are there in SQL?

two types

There are two types of Indexes in SQL Server: Clustered Index. Non-Clustered Index.
Indexes in MySQL Tutorial

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *