MariaDB Create Index

Summary: in this tutorial, you will learn how to use the MariaDB create index statement to create a new index to improve the speed of queries.

Introduction to database indexes

A database index is similar to a book index that helps you find information quicker and easier.

See the following countries table from the sample database:

select * 
from countries;
Code language: SQL (Structured Query Language) (sql)

If you want to find a country with a specific name, you use a where clause as follows:

select *
from countries
where name = 'France';
Code language: SQL (Structured Query Language) (sql)

To find the France country, MariaDB, or precisely the query optimizer, has to scan all rows in the countries table. Since the table countries does not have many rows, the speed of the query is acceptable. However, if the table is big with millions of rows, the query will be very slow.

The query optimizer is a database software component in MariaDB that determines the most efficient way of executing a query to access requested data.

In MariaDB, you can use the explain keyword before any select statement to show the information on the query execution plan:

explain
select * 
from countries
where name = 'France';
Code language: SQL (Structured Query Language) (sql)

As you can see from the output, the number of rows that the query optimizer has to examine is specified in the rows column, which is the same as the number of rows in the countries table.

To create an index, you use the create index statement:

create index index_name
on table_name(column_list);
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the index in the create index clause.
• Second, specify the name of the table and a list of comma-separated column names in the on clause. The column list that you specify in the parentheses will be included in the index.

MariaDB create index statement example

For example, the following statement creates an index that includes the name column of the countries table:

create index country_name 
on countries(name);
Code language: SQL (Structured Query Language) (sql)

Once you execute the create index statement, MariaDB creates a separate data structure to store a copy of the values of the name column.

This data structure is called a balanced tree or simply called b-tree. In the b-tree structure, the country names are stored in a sorted order that is optimized for locating information quickly and efficiently.

The following shows the query execution plan of the query that finds a country by name when an index is available:

explain
select * 
from countries
where name = 'France';
Code language: SQL (Structured Query Language) (sql)

As you can see clearly from the output, the query optimizer leverages the index and examines just one row, not all rows, to return the result.

Even though an index can help improve the performance of a query, it comes with costs:

• Storage space to maintain the index data structure. In the above example, MariaDB uses storage for storing sorted country names separately.
• Write overhead when the data in the indexed column changes. If you insert or update values in the indexed columns, MariaDB needs to maintain the data in the index correspondingly.

Therefore, you only create indexes for columns frequently used as the selection criteria in the select statement.

MariaDB primary vs. secondary index

When you create a table with a primary key, MariaDB implicitly creates an index called PRIMARY that includes all the primary key columns.

The PRIMARY index is special because of two reasons:

• First, a primary index is stored together with the data within the same table, not in a separate data structure. A PRIMARY index is also known as a clustered index.
• Second, a primary index is only can be created implicitly via a primary key constraint.

An index created by the create indexstatement is called a secondary index.

In this tutorial, you learned how to use the MariaDB create index statement to create a new index to speed up queries.