MariaDB Alter Table Add Index

In this MariaDB tutorial, we will understand how to add the indexes to the existing table to speed up the search of the records in the database.

While I was searching for the detail of the customers in my database, it was very time-consuming because the table of the database wasn’t indexed properly or the table was without any index. The use of indexes allows for quicker data retrieval from databases. The indexes are used only to accelerate searches and queries, they are invisible to users.

So to get the result a faster way and for better performance, we should always index our databases or tables. In this section we will learn how to add the index to the existing table, additionally, we will cover the following topics.

• Implementation of MariaDB Alter Table Add Index
• Example of MariaDB Alter Table Add Index Drop
• Example of MariaDB Alter Table Add Unique Index
• How to MariaDB Alter Table Add Index as Column_Names
• How to MariaDB Alter Table Add Index If Not Exists
• How to MariaDB Alter Table Add Index Json Column
• MariaDB Alter Table Add Index Multiple Columns
• How to MariaDB Alter Table Add Index on temporary
• Example of MariaDB Alter Table Add Index Primary Key
• MariaDB Alter Table Add Index Syntax Error

MariaDB Alter Table Add Index

You can alter a table’s structure with the ALTER TABLE command. You can, for instance, create or remove indexes and add or remove columns. First, we need to know “What are indexes?”.

When using particular columns in a database array, database table indexes can increase the pace of search queries. In essence, an index is a sort of table that has been optimized for the column or columns that are given to it. For each defined index, a new index table is constructed, these tables are hidden from all database users.

• Look at the data and columns to decide which ones will be needed to build search queries before you create an index. On the basis of those columns, you should create one or more indexes.

To see the current indexes that are associated with a table, use the below command syntax.

SHOW INDEXES FROM tbl_name;

For example, we have table users in our database, to see its indexes use the below command.

SHOW INDEXES FROM users;

The above output shows the index of the table users as we can see the index_type is BTREE of column id.

• When choosing an index’s name, keep in mind that all other administrators and you need to know the name. They are neither used outside of database syntax nor are they column names. This implies that you can give it any name you wish.
• It is very usual to take the name of the column when naming an index when there is just one column involved.

If you need to add an index to a table that is already existing, use the below syntax.

ALTER TABLE tble_name ADD INDEX idx_name (col_name1, col_name2, ...);

Where,

• ALTER TABLE: It is the command to make changes to the table.
• tbl_name: On which table you want to create an index, specify the name of the table.
• ADD INDEX: It is the command to create a new index.
• idx_name: It is the name of the index that we want to create.
• (col_name1, col_name2, ..): These are the names of the columns that are going to act as an index for the table.

Let’s take an example by following the below steps:

Below is the description of the table that we are going to use as an example for this section.

Let’s take a column name and make it an index of the table using the below statement.

ALTER TABLE users ADD INDEX user_name(name);

Now check the indexes of the table users using the below command.

SHOW INDEXES FROM users;

From the above output, we can see that the column name is an index of the table users with index name user_name and index type BTREE. This is how to add the index to the existing table of the MariaDB using the ALTER TABLE statement.

Read: MariaDB Add Auto Increment Column

MariaDB Alter Table Add Index Drop

We have learned how to add an index to the existing table using the ALTER TABLE statement. In this section, we will discuss how to drop the indexes from the table.

While creating an index speeds up searches, it also makes other operations like running UPDATE or INSERTS commands against the table take longer. To improve insertion and update performance, you could choose to eliminate an index if you discover that it is not frequently used.

The syntax is given below.

ALTER TABLE tbl_name DROP INDEX idx_name;

Where,

• ALTER TABLE: It is the command to make changes to the table.
• tbl_name: Name of the table whose index will be removed.
• DROP INDEX: It commands to remove the index from the table.
• idx_name: It is the name of the index that you want to remove.

Let’s take an example and remove the index user_name that we have created on table users in the above subsection.

First check the index name that we are going to drop.

SHOW INDEXES FROM users;

Execute the below query to drop the index of the table users.

ALTER TABLE users DROP INDEX user_name;

After running the above code, check the indexes from the table users.

From the above output, we have dropped the index user_name. This is how to drop the index of the table using the ALTER TABLE statement in MariaDB.

Read: MariaDB Str_To_Date

MariaDB Alter Table Add Unique Index

A unique index can be added to a table in addition to a conventional index. In MariaDB, the unique index and the primary key are interchangeable. Based on the unique index, each possible combination of values in the indexed columns must be distinct. Unique indexes are essential for both quick performance and the preservation of the accuracy of the data in the table.

The syntax is given below.

ALTER TABLE tble_name ADD UNIQUE INDEX idx_name (col_name1, col_name2, ...);

Where,

• ALTER TABLE: It is the command to make changes to the table.
• tbl_name: On which table you want to create an index, specify the name of the table.
• ADD UNIQUE INDEX: It is the command to add a new unique index.
• idx_name: It is the name of the index that we want to create.
• (col_name1, col_name2, ..): These are the names of the columns that are going to act as an index for the table.

Let’s take an example by following the below steps:

Use the below command to add the unique index to the table users.

ALTER TABLE users ADD UNIQUE INDEX user_name(name);

Check the created index on table users using the below command.

SHOW INDEXES FROM users;

From the above output, we have added the unique index to the table users. This is how to add the unique index to the table using the ALTER TABLE statement in MariaDB.

Read: MariaDB Unique Key

MariaDB Alter Table Add Index as Column_Names

In this section, we will add an index to a table with the same name as the column which is going to be the index of the table. The syntax for adding the index is the same as we have learned in the above subsections.

Let’s take an example and see how to add an index to an existing table.

ALTER TABLE users ADD INDEX name(name);

Check the created index using the below command.

SHOW INDEXES FROM users;

The above output shows the index name as a column name. This is how to add the index as column names using the ALTER TABLE statement in MariaDB.

Read: MariaDB Difference Between Two Dates

MariaDB Alter Table Add Index If Not Exists

Before adding an index, you can check to see if one with the same name already exists in the table in MariaDB by using the IF NOT EXISTS clause of the ALTER TABLE statement.

The syntax is given below.

ALTER TABLE tble_name ADD INDEX IF NOT EXISTS idx_name (col_name1, col_name2, ...);

Where,

• ALTER TABLE: It is the command to make changes to the table.
• tbl_name: On which table you want to create an index, specify the name of the table.
• ADD INDEX: It is the command to create a new index.
• IF NOT EXISTS: To check to see if one index with the same name already exists in the table or not.
• idx_name: It is the name of the index that we want to create.
• (col_name1, col_name2, ..): These are the names of the columns that are going to act as an index for the table.

Let’s take an example by following the below steps:

Use the below command to create an index on table users.

ALTER TABLE users ADD INDEX IF NOT EXISTS name(name);

As soon as we run the above command to create an index named name on the table users, before creating it checks whether the same name of the index already exists on the table user or not. If exist then it shows a warning as shown in the below picture.

The above output shows that the same name of the index already exists on the table users. So to create an index of the same name, first drop the old one.

Read: MariaDB Date Greater Than 

MariaDB Alter Table Add Index Json Column

The acronym JSON stands for JavaScript Object Notation. It is a simple data exchange format that humans can read and write that is similar to other data types. Machines can readily parse and generate it as well.

The JSON data type typically supports two structures. A chain of name-value pairs that serves as a data array. A set of values arranged in a list. In this section, we will learn how to use the column of type JSON as an index for the table.

The syntax is given below.

ALTER TABLE tble_name ADD INDEX idx_name (json_col_name1, json_col_name2, ...);

Where,

• ALTER TABLE: It is the command to make changes to the table.
• tbl_name: On which table you want to create an index, specify the name of the table.
• ADD INDEX: It is the command to create a new index.
• idx_name: It is the name of the index that we want to create.
• (json_col_name1, json_col_name2, ..): These are the names of the JSON columns that are going to act as an index for the table.

Let’s take an example by following the below steps:

First, create a table users using the below command.

CREATE TABLE users(userdetail JSON, country VARCHAR(200), email VARCHAR(300));

Insert the following records into the table.

INSERT INTO users VALUES ('{"id": 1, "name": "Oliver"}',"USA", "oliver23@gmail.com"),
                          ('{"id": 2, "name": "Monty"}',"USA", "Monty@gmail.com"),
		        ('{"id": 3, "name": "Liam"}',"United Kindom", "liam@gmail.com");

View the created table using the below query.

SELECT * FROM users;

From the above output, we can see that the column userdetail contains the JSON data, So we are going to use this column for the index of the table.

Check if any index is available on the table users using the below query.

SHOW INDEXES FROM users;

The above query shows that the table users don’t have any index, So add an index to the table using the below command.

ALTER TABLE users ADD INDEX user_details(userdetail);

Check the created index using the below command.

SHOW INDEXES FROM users;

The above output shows the index user_details of type JSON on table users. This is how to add an index on a column of type JSON to a table.

Read: MariaDB Check Constraint + Examples

MariaDB Alter Table Add Index Multiple Columns

Till now we have learned from the above subsection how to add a single index to the table, Now we will learn how to add multiple indexes to the table using the ALTER TABLE.

To add multiple indexes, we need to provide the name of multiple columns. The syntax is given below.

ALTER TABLE tble_name ADD INDEX idx_name (col_name1, col_name2, ...);

Where,

• ALTER TABLE: It is the command to make changes to the table.
• tbl_name: On which table you want to create an index, specify the name of the table.
• ADD INDEX: It is the command to create a new index.
• idx_name: It is the name of the index that we want to create.
• (json_col_name1, json_col_name2, ..): These are the names of the multiple columns that are going to act as an index for the table.

Let’s take an example by following the below steps:

We are going to use the same table users that we have created in the above subsections. So use the command to add multiple indexes to that table.

ALTER TABLE users ADD INDEX country_email(country, email);

Check the multiple created indexes using the below command.

SHOW INDEXES FROM users;

The above output shows the multiple indexes on table users. This is how to add multiple indexes to the table using ALTER TABLE statement in MariaDB.

Read: MariaDB Greatest Function

MariaDB Alter Table Add Index on temporary

A temporary table in MariaDB is a particular kind of database table that enables us to temporarily store resultsets. Additionally, we can make use of the temporary result numerous times throughout the same session. However, the temporary table is also automatically destroyed when the current sessions expire.

In this section, we will add the index to the temporary table. If you want to know more about temporary tables, then visit our tutorial on MariaDB Temporary Table. The syntax to add an index to the temporary table is the same as we have learned above subsections.

Let’s take an example by following the below steps:

First, create a temporary table users using the below command.

CREATE temporary TABLE users(userdetail JSON, country VARCHAR(200), email VARCHAR(300));

Insert the following records into the table.

INSERT INTO users VALUES ('{"id": 1, "name": "Oliver"}',"USA", "oliver23@gmail.com"),
                          ('{"id": 2, "name": "Monty"}',"USA", "Monty@gmail.com"),
			 ('{"id": 3, "name": "Liam"}',"United Kindom", "liam@gmail.com");

View the created temporary table.

SELECT * FROM users;

Use the below command to add index email on the table users.

ALTER TABLE users ADD INDEX u_email(email);

Check the created index on the table users.

SHOW INDEXES FROM users;

From the above output, we have added the index to the temporary table users. This is how to add the index to the temporary table using the ALTER TABLE statement in MariaDB.

Read: MariaDB Median

MariaDB Alter Table Add Index Primary Key Example

We are going to cover how to add an index primary key to the table. Primary keys are special and cannot ever be null. Since only one record will ever be identified, every record must be represented. There can be only one primary key per table.

The syntax is given below.

ALTER TABLE tble_name ADD PRIMARY KEY (col_name);

Where,

• ALTER TABLE: It is the command to make changes to the table.
• tbl_name: On which table you want to create an index, specify the name of the table.
• ADD PRIMARY KEY: It is the command to create a new primary key index.
• (col_name1): The name of the column that is going to act as the primary key index for the table.

Let’s take an example by following the below steps:

Use the table users for the example that we have created in the above subsections. The below command adds the primary key index to that table.

ALTER TABLE users ADD PRIMARY KEY (email);

View the index of the table using the below command.

SHOW INDEXES FROM users;

The above output shows the primary key index PRIMARY on the table users. This is how to add the primary key index to the table in MariaDB.

MariaDB Alter Table Add Index Syntax Error

In MariaDB, while adding an index if you get a syntax error, then follow the MariaDB documentation for the proper guidance. Otherwise check the above subsections “MariaDB Alter Table Add Index”.

When a user writes syntax incorrectly or accidentally enters anything incorrectly, this type of error is produced. Looking at the syntax for adding the index to the table will help you overcome this kind of issue. However, you can refer to the right syntax for adding the index, which is shown below, when adding the syntax to the table.

ALTER TABLE tble_name ADD INDEX idx_name (col_name1, col_name2, ...);

Where,

• ALTER TABLE: It is the command to make changes to the table.
• tbl_name: On which table you want to create an index, specify the name of the table.
• ADD INDEX: It is the command to create a new index.
• idx_name: It is the name of the index that we want to create.
• (col_name1, col_name2, ..): These are the names of the columns that are going to act as an index for the table.

You may also like to read the following MariaDB tutorials.

• MariaDB Not Between
• MariaDB GROUP BY
• MariaDB JSON Data Type
• MariaDB Set Auto Increment Value
• MariaDB Not Equal Operator

We have learned how to add the index to the table and different kinds of indexes. Also learned how to add the index to the temporary table, and JSON columns, and covered how to drop the index by following the below topics.

• Implementation of MariaDB Alter Table Add Index
• Example of MariaDB Alter Table Add Index Drop
• Example of MariaDB Alter Table Add Unique Index
• How to MariaDB Alter Table Add Index as Column_Names
• How to MariaDB Alter Table Add Index If Not Exists
• How to MariaDB Alter Table Add Index Json Column
• MariaDB Alter Table Add Index Multiple Columns
• How to MariaDB Alter Table Add Index on temporary
• Example of MariaDB Alter Table Add Index Primary Key
• MariaDB Alter Table Add Index Syntax Error