MySQL MariaDB

How Do I Find the Index of a Table in MySQL?

A databank index is a data arrangement that enhances the swiftness of table transactions. Having one or even more columns, indexes may be generated, laying the groundwork for mutually rapid random searches and effective record entree arrangement. This should be kept in mind when constructing an index, that all columns are cast-off to generate SQL queries, and also to construct one or even further indexes for those columns. In practice, indexes are a form of tables that hold the primary key or index column field and reference the individual table for every record. Users may not see the indexes, these are cast-off to accelerate queries, as well as the Database Search Engine, would consume them to trace records very quickly.

Indexes Using MySQL WorkBench

Firstly, start your MySQL Workbench and connect it with the root database.

We will be creating a new table ‘contacts’ in the database ‘data’  having different columns in it. We have one primary key and one UNIQUE key column in this table, e.g. id and email. Here, you have to clarify that you don’t need to create indexes for the UNIQUE and PRIMARY key columns. The database automatically creates the indexes for both types of columns. So we will be making index ‘phone’ for the column ‘phone’ and index ‘name’ for the columns’first_name’ and  ‘last_name’. Execute the query using the flash icon on the taskbar.

You can see from the Output that the table and indexes have been created.

Now, navigate towards the schema bar. Under the ‘Tables’ list you can find the newly created table.

Let’s try the SHOW INDEXES command to check indexes for this particular table as shown below in the query area using the flash sign.

This window will appear at once. You can see a column ‘Key_name’ which shows the key belongs to every column. As we have created the ‘phone’ and ‘name’ index, it appears as well. You can see the other relevant information regarding indexes e.g., sequence of the index for a particular column, index type, visibility, etc.

Indexes Using MySQL Command-Line Shell

Open the MySQL command-line client shell from your computer. Enter the MySQL password to start using.

Example 01
Assume that we have a table ‘order1’ in the schema ‘order’ with some columns having values as illustrated in the image. Using the SELECT command, we have to fetch the records of ‘order1’.

>> SELECT * FROM data.order1;

As we haven’t defined any indexes for the table ‘order1’ yet, it is impossible to guess. So we will be trying the SHOW INDEXES or SHOW KEYS command to check the indexes as follows:

>> SHOW KEYS FROM order1 IN data;

You can perceive that table ‘order1’ has only 1 primary key column from the below output. This means that there are no indexes defined yet, that’s why it is showing only 1-row records for the primary key column ‘id’.

Let’s check the indexes for any column in the table ‘order1’ where the visibility is off as shown below.

>> SHOW INDEXES FROM data.order1 WHERE VISIBLE =NO;

Now we will be creating some UNIQUE indexes on the table ‘order1’.  We have named this UNIQUE INDEX as ‘rec’ and applied it to the 4 columns: id, Region, Status, and OrderNo. Try the below command to do so.

>> CREATE UNIQUE INDEX rec ON data.order1 (id, Region, Status, OrderNo);

Now let’s see the result of creating the indexes for the particular table. The result is given below after the use of the SHOW INDEXES command. We have a list of all the indexes created, having the same names ‘rec’ for each column.

>> SHOW INDEXES FROM order1 IN data;

Example 02
Assume a new table ‘student’ in the database ‘data’ with four-column fields having some records. Retrieve the data from this table using the SELECT query as follows:

>> SELECT * FROM data.student;

Let’s fetch the primary key column indexes first by trying the below SHOW INDEXES command.

>> SHOW INDEXES FROM data.student WHERE Key_name = ‘PRIMARY’;

You can see it will output the index record for the only column having the type ‘PRIMARY’ due to the WHERE clause used in the query.

Let’s create one unique and one non-unique index on the different table ‘student’ columns. We will first create the UNIQUE index ‘std’ on the column ‘Name’ of the table ‘student’ by using the CREATE INDEX command on the command-line client shell as below.

>>  CREATE UNIQUE INDEX std ON data.student ( Name );

Let’s create or add a non-unique index on the column ‘Subject’ of the table ‘student’ while using the ALTER command. Yes, we have been using the ALTER command because it is used to modify the table. So we have been modifying the table by adding indexes to the columns. So let us try the below ALTER TABLE query in the command-line shell add the index ‘stdSub’ to the column ‘Subject’.

>> ALTER TABLE data.student ADD INDEX stdSub ( Subject );

Now, it’s the turn to check for the newly added indexes on the table ‘student’ and its columns ‘Name’ and ‘Subject’. Try the below command to check through it.

>>  SHOW INDEXES FROM data.student;

From the output, you can see that the queries have assigned the non-unique index to the column ‘Subject’ and the unique index to the column ‘Name’. You can also see the names of the indexes.

Let’s try the DROP INDEX command to drop the index ‘stdSub’ from the table ‘student’.

>> DROP INDEX stdSub ON data.student;

Let see the remaining indexes, using the same SHOW INDEX instruction as below. We have now left with the only two indexes remained in the table ‘student’ as per the below output.

>> SHOW INDEXES FROM data.student;

Conclusion

Finally, we have done all the necessary examples about how to create unique and non-unique indexes, show or check indexes, and drop the indexes for the particular table.

About the author

Aqsa Yasin

I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.