Showing indexes in MariaDB

In the area of database optimization, indexes play an important role in enhancing performance and speeding up query execution. MariaDB which is a powerful open–source relational database system that offers several indexing options to fast the data retrieval and boost efficiency. In this article, we will learn about What are SHOW INDEX and how to use them along with the examples and so on.

What is Create Index in MariaDB?

CREATE INDEX is a statement used to create an index on a table. Indexes are used to improve the speed of data retrieval operations, such as SELECT queries, by allowing the database to quickly locate rows based on the indexed columns.

To understand how to SHOW INDEX in MariaDB we need a table on which we will perform various operations and queries. Here we will consider a table called employees which contains customer_id, name, phone_number and country as Columns.

customers table

Syntax:

CREATE INDEX index_name ON table_name (column_name);

Let us create an index on the column country in the customers table mentioned above.

CREATE INDEX country_index on CUSTOMERS ( country );

Output:

creating an index

From above query, we can observe that the index has been created on the country column in the customers table. Now to see how this effected the table or the country column, we can use “DESC table_name” statement.

Describe table

As we can see in the above description of the table, the key field of the column country contains “MUL” value which tells that the column is part of a non-unique index i.e. the column contains multiple entries having the same value. In the same way we can create an unique index ( column contains unique records ) which represents “UNI” in the key field when describing the table. we can view the same in the below result image.

Let’s create one more index to get good understanding.

CREATE UNIQUE INDEX id_index ON customers ( customer_id);

Output:

customer_id index

How to View Indexes in MariaDB?

Now that we have created some indexes on the columns in the customers table, we can view the created indexes using the SHOW INDEX statement. The SHOW INDEXES allows us to query the indexes or view the indexes in a database table;

Syntax:

SHOW INDEXES FROM table_name;

Suppose, If we are not connected to any database then the following statement can be used to view the indexes or query the indexes in a table;

SHOW INDEXES FROM table_name IN data_base_name;
(or)
SHOW  INDEXES FROM database_name.table_name;

We can use “KEYS” or “INDEX” keyword instead of “INDEXES” in SHOW INDEXES statement.

SHOW KEYS IN database_name.table_name;
(or)
SHOW INDEX FROM database_name.table_name;

All the SHOW statements specified above return the same result i.e. returns the information on the indexes created on a table in a database.

Output:

show indexes output

Explanation: As we can saw in the image that all of the created index is shown in the image which are country_index, id_index.

Conclusion

Overall, indexes are important for optimizing database performance, especially in MariaDB. The CREATE INDEX statement allows us to create indexes on specific columns, improving data retrieval speed for SELECT queries. Additionally, the SHOW INDEX statement provides a convenient way to view the indexes created on a table, helping us understand the index structure and its impact on query performance.