A Data Scientist or a programmer will have to work with large quantities of data and needs to be able to deal with it efficiently for faster execution. They also need to know how the data is organised and the fastest methods to easily access the particular data needed.
Since MySQL is a relational database management system that has applications in various sectors such as web databases, e-commerce applications, social media etc. data scientists need to know all the techniques related to MySQL, one of which is indexes. The article describes how to create Index in MySQL.
Check out our free courses to get an edge over the competition.
What is an Index?
In MySQL, Indexes are used to sort data in an organised sequential way and retrieve the data efficiently and quickly. They are created by using one or more columns that will be used to filter data. Indexes are a type of table that keep a primary or index field and a pointer to each record in the main table.
For a better understanding, a book index can be used as an example. Instead of looking for a page by going through all the pages in a book, the book index can be checked to find the page one is looking for.
In most database systems, high and fast performance is needed. Many businesses invest huge sums of money on hardware for faster data retrievals and manipulations. But by optimizing the database, the costs can be reduced.
Response time is usually longer because records are stored randomly, and search queries have to find the needed data by looping through the entire stored records. Indexes make this search easier and faster.
Explore our Popular Software Engineering Courses
Check out upGrad’s Advanced Certification in Cyber Security
Read: Rename Column name in SQL
You can create index in MySQL on a table by using the following basic syntax.
CREATE INDEX index_name table_name (column_name);
You can also create a unique index on a table. This means that the name is unique, and any two rows cannot have the same name. The following syntax can be used for this.
CREATE UNIQUE INDEX index_name ON table_name (column_name);
If a secondary index for a column is created, MySQL stores the values in a separate data structure such as B-Tree and Hash. In MySQL, if the columns are string columns, you can create an index for the leading part of the column values of the string column using the syntax:
To create the column prefix key part when the table is created, the following syntax can be used:
CREATE TABLE table_name(
And to add an index to an existing table:
CREATE INDEX index_name ON table_name (column_name(length));
The length here is the number of characters if it is a non-binary string type like CHAR, VARCHAR and TEXT and the number of bytes if it is binary string type like BINARY, VARBINARY and BLOB. You can also create column prefix key parts for CHAR, VARCHAR, BINARY, and VARBINARY columns in MySQL. It is necessary to specify the column prefix key parts if you have created indexes for BLOB and TEXT columns.
Check out upGrad’s Advanced Certification in Cloud Computing
In case of FULLTEXT indexes that are only supported for InnoDB and MyISAM tables, they can include only CHAR, VARCHAR and TEXT columns. It does not support column prefix indexing and if any prefix length is specified, it is ignored.
In-Demand Software Development Skills
Must Read: SQL Project Ideas
Altering tables to add or drop Index
To add indexes to a table, there are four types of statements that can be used:
- To add a primary key:
ALTER TABLE table_name ADD PRIMARY KEY (column_list);
The indexed values here should be unique and cannot be NULL.
- For an index with unique values:
ALTER TABLE table_name ADD UNIQUE index_name (column_list);
The values should be unique, except for NULL which can appear multiple times.
- To add an ordinary index:
ALTER TABLE table_name ADD INDEX index_name (column_list);
Here the values can appear more than once.
- To create special FULLTEXT index:
ALTER TABLE table_name ADD FULLTEXT index_name (column_list);
The FULLTEXT index can be used for text searching purposes.
In existing tables
To add index in any existing table, the following syntax can be used:
ALTER TABLE table_name ADD INDEX (index_name);
To delete an index in a table, the DROP INDEX statement is used:
ALTER TABLE table_name DROP INDEX (index_name);
Display Index Information
To list all the indexes associated with any table, the SHOW INDEX command is used.
SHOW INDEX FROM table_name\G
The ‘\G’ is used to create the list in a vertical format, this avoids the long line wraparound.
Also Read: SQL Interview Questions & Answers
InnoDB can store the entries in descending order when the index is a descending index. So, when the descending order is requested in the query, the optimizer chooses this index. This is more efficient for queries with ORDER BY clauses. These are only supported by the InnoDB storage engine.
The syntax for creating or adding descending indexes is like alter or create syntaxes used above.
ALTER TABLE table_name ADD index_name (col1_name desc, col2_name asc);
CREATE INDEX index_name ON table_name (col1_name desc, col2_name asc);
upGrad’s Exclusive Software Development Webinar for you –
SAAS Business – What is So Different?
To mark indexes as unavailable for the query optimizer, invisible indexes can be used. MySQL updates invisible indexes when the data in columns associated with the index changes. Indexes are visible by default and to make them invisible, you have to explicitly declare the visibility at the time of creation or by using the ALTER TABLE command.
To create an invisible index, the following syntax is used:
CREATE INDEX index_name ON table_name (c1, c2…) INVISIBLE;
To change the visibility of existing indexes, the following syntax in used:
ALTER TABLE table_name ALTER INDEX index_name [VISIBLE | INVISIBLE];
Enrol in Online Software Development Courses from the World’s top Universities. Earn Executive PG Programs, Advanced Certificate Programs, or Masters Programs to fast-track your career.
Explore Our Software Development Free Courses
|Blockchain Technology||React for Beginners||Core Java Basics|
The performance of MySQL search queries can be improved by using Indexes. These can be defined when a table is created or can be added later if the table is already created.
This article gave a brief description of what an index is and why it is used. The next section explains how to create index in MySQL, how it can be added to an existing table and how to create a unique index.
The different commands to alter tables and create or drop an index were also described along with displaying index information. Descending and invisible indexes were also briefly explained.
All in all, learning and getting hands-on practice on all the databases mentioned in this article will add to your experience. Go through the course overview, learning, and job opportunities involved with it—platforms like upGrad offer power-packed courses designed by the highly reputed institutes like IIIT-B in Executive PG Program Full Stack Development.
How are clustered and non-clustered indexes defined in My SQL?
MySQL has mainly two types of indexes: clustered index and a non-clustered index. Although both are involved primarily in data sorting, their approach is quite different. A clustered index is created when a primary key is clarified. A clustered index is unique and limited to one per table. This is because clustered indexes can only sort the data in ascending order. Hence, the rows in the table will be presented in one way only. At the same time, a non-clustered index sorts the data through specific pointers in a separate field distinct from the table. They are also not limited to one index per table, giving greater access to the data.
How is indexing different from hashing?
Indexing and hashing have widely different functions, although applied to the same data set. They mainly differ in their approach to locating data. Indexing involves reducing the query process. It helps procure desired data points from an extensive data set using different types of indexes. On the other hand, hashing doesn’t require an index structure to procure data. It uses mathematical functions with a hash prefix to pinpoint data in the sheet. In humongous data sheets where indexing might be tedious, hashing has a faster process. It assigns a short hash key to each data point and locates them instantly.
What are the limitations of indexing in MySQL?
Indexes reduce the hassle required to navigate databases and to locate specific data. They assist in browsing through large data sheets, resulting in quicker identification, increased time efficiency, and optimized queries. However, it has its limitations. When it comes to very large datasheets, indexing can become complicated and take up more memory space than a relatively more minor datasheet. Hence, as the data volume increases, maintaining and keeping track of the indexes becomes more complex. In case of any alterations within the sheet, the index key also has to be modified. Nevertheless, the convenience that indexing provides overtakes its drawbacks.