Working with Index in MySQL
Introduction:
Indexes are special lookup tables that are created so that the database search engine can use them to speed-up data retrieval. Basically, a file is a pointer to information in a table. It is similar to a book index which points towards specific information using some key which helps us to easily locate the page with the required information. For instance, on the off chance that you need to reference all pages in a book that talks about a specific theme, you initially allude to the file, which records all the subjects in order and is then alluded to at least one explicit page numbers.
An index record assists with accelerating SELECT and WHERE queries, however, it hinders information contribution, with the UPDATE and the INSERT articulations. Indexes can be made or dropped with no impact on the information or actual data-set.
To create a simple index table we follow the given syntax:-
create index index_name on tab_name(col_names);
Remember that more than one column can be used to create an index table which makes different kinds of index tables that we will study later in this article.
To perform search queries on the index table binary search works. Also, a unique SQL keyword is used to declare an index as unique which prevents duplicate entries in the index table. Unique helps to prevent a record from entering two similar values in the same column.
Description:
A Index table is a view that speeds the recovery of column values from the table or view. A record contains keys worked from at least one section in the table or view. These keys are put away in a structure (B-tree) that empowers SQL Server to discover the column or lines related to the key qualities rapidly and effectively.
A table or view can contain the accompanying kinds of records:
Types based on function and syntax :
- Simple composite unique Index:
A simple index contains keys for only one column value but in a composite index, there are two columns.
Syntax : Create unique index index_name on table_name(col1,col2s);
2. Reverse Key Index:
It is used to reduce the block contention by reducing the bytes of keys in the values in the index.
It is created using the keyword REVERSE.
Syntax: Create index I on tab(a,b) Reverse;
3) Bitmap Index:
It is like a map of values in a two-dimensional structure.
Syntax: create bitmap index index_name on table_name(col_name);
4) Function-based Index:
Such indexes are created using functions.
Syntax : create index index_name on tab_name(func_name(col_name));
There are more indexes like key compressed index too.
Dropping Index:
To drop it mean to remove a index table by deleting it. If a index is created by primary keys or unique constraints then we first need to use alter keyword and then use drop with it.
To drop an index table that is created without using unique constraints and primary key use the following syntax.
Syntax: Drop index index_name on table_name;
Note: First you should have Drop Any Index privilege in MySQL.
Rename Index:
Syntax: ALTER TABLE user DROP INDEX newindex, ADD INDEX oldindex(password);
So, now that we have studied index and its types let's discuss when we should create indexes and when to avoid them.
When should we create indexes:
1) If there are large values in column
2) If the column doesn’t have any null values
When we should avoid indexes:
1) If table is small
2) If a column is updated frequently
EXAMPLE:
We will create a new table named user to demonstrate the create, rename, and drop command on indexes:
Now we will use the insert keyword to insert data into the user table :
Ex: insert into table user values(‘Vikrant’, ‘myword’,44);
Following is a simple program to create, rename, and drop a index in the above user table:
The following command returns all index information from the user table:
Show indexes from user;
RESULT:
Now let us see the result of the query to perform rename of index newindex from user table:
Similarly, when we perform the show query after dropping the index table we get an empty table with no index tables in it. Also from the output, we get to know that the type of index is Btree,
CONCLUSION:
Indexes in a database can be very interesting and easy to use. The most beautiful part of using an index is that it won’t affect our dataset in any condition. We can use indexes to make search queries faster which will help us in analysis of large data using sql. Indexes like the primary key index and unique index help us to avoid duplicate row data. Also to search a text data on large data of text is possible using indexes. There are different types of indexes that one can create and use according to the convenience.
