MariaDB: Adding and Removing Indexes

Database tables often need altering during their life cycle. As your needs change, the data you store will often need to change to reflect these needs. Perhaps you recently added a column to your database, or you find out that certain columns are getting searched. In this article, we will explore how to add and remove indexes for a database table using MySQL or MariaDB management systems.

Topics Include

• What is an Index?
• Viewing Indexes on a Table
• Naming an Index
• Multi-column Index
• Adding an Index to an Existing Table
• Removing an Index

Use database indexes to optimize the database operations on your Dedicated Hosting server!

What is an Index?

Database table indexes help improve the speed of search queries when using certain columns in a database array. An index is effectively a type of table, optimized for the column or columns assigned to it. A separate index table is created for each defined index and is invisible to all users of the database.

Before you create an index, take a look at the data and columns to consider which ones will be used to create search queries. You will want to make one or more indexes based on those columns.

Viewing Indexes on a Table

SHOW INDEXES FROM table_name;

SHOW INDEXES FROM user;

Naming an Index

When deciding to name an index, remember it is only a name that you and other administrators need to understand. They are not column names, nor are they seen or used outside of the database syntax. This means you can name it whatever you want. However, naming an index for a user_name column “fred” is far less descriptive than naming it “user” or sticking with “user_name”.

When naming an index that only has one column involved, it is very common to use the name of the column. For instance, if you want to create an index for the “last_name” column, you would simply name it “last_name” when creating the index. The single column index is the most common, so most of them are very descriptive when named this way.

If you have an index that involves more than one column, make sure to give it a descriptive name. For example, if you create an index that involves the “street_number” and “street_name” columns, you may want to name it “address”.

Multi-column Indexes

Using multiple columns to create an index is not as common as using single columns, but it can be done if necessary. When assigning more than one column to an index, remember that the left most column is the base for the index.

If you create an index called “name” that includes (“last_name”, “first_name”), then the index is built based on the last_name column. This means that it optimizes search queries that are looking for the last_name AND the first_name columns, or simply just the last_name column. It does not optimize search queries looking for only the first_name column.

Adding an Index to an Existing Table

If you have an existing table on which you want to add an index, use the following template for your SQL statement:

CREATE INDEX index_name ON table_name(column_name1, column_name2, etc);

For our example, say we have a table named “user” with the following columns: (id, first_name, last_name). The table has a primary key that was added upon creation for the “id” column. We decided to add an index for the “first_name” column. We also decided to give this index the name “first” instead of sticking with the column name convention mentioned above. This is so you can see the difference when the data is displayed later. The statement to create this index will look like this.

CREATE INDEX first ON user(first_name);

When creating a multi_column index based on last_name and first_name it would look like this:

CREATE INDEX name ON user(last_name, first_name);

After running the single column example from above and receiving no errors, run the SHOW INDEXES statement for the “user” table. You will see that the new index was added.

Removing an Index

While adding an index does increase the speed of search, it also slows down other things such as running INSERT or UPDATE statements against the table. If you find that an index is not used very often, you may want to remove it to help increase insertion and update speeds.

Removing an index is just as easy as adding one, but with a slightly different command statement. Below is the command template you will use to remove an index.

ALTER TABLE table_name DROP INDEX index_name;

To continue with our current example, we will be removing the index we created earlier, named “first”. The command statement will look like this:

ALTER TABLE user DROP INDEX first;

After running that statement and receiving no errors, run the SHOW INDEXES statement once again. You will see the “first” index has been removed and the list now only shows the original primary index.

Now you understand how and when to add and remove indexes to your database tables. This should help you in optimizing your database speeds to help increase overall performance for your application or website.