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.
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:
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.
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:
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.