MariaDB index with Examples - DatabaseFAQs.com

In this MariaDB tutorial, we will learn about the “MariaDB index“, here we will create a different kind of index to retrieve the records or information in an efficient way and cover the following topics.

MariaDB create index
MariaDB index types
MariaDB create index multiple columns
MariaDB create index if not exists
MariaDB create index no lock
MariaDB create index using btree
MariaDB index json or json column
MariaDB index column
MariaDB index hint
MariaDB index statistics
MariaDB index on view
MariaDB index length and index size

Table of Contents

MariaDB Create Index

In this section, we will go through the MariaDB CREATE INDEX statement and explain it with syntax and examples.

The MariaDB database index is a type of book index from which we will find information quicker and easier. To create an index, we use the CREATE INDEX statement.

SYNTAX:

CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN_LIST);

The syntax explanation:

First, specify the index_name in the CREATE INDEX clause.
Second, specify the table_name and a list of comma-separated column_name in the ON clause. The index must include the column list that we must define in the parenthesis.

To begin, run the following query to generate a USA_STATES table:

CREATE TABLE USA_STATES(
STATE_ID INT AUTO_INCREMENT PRIMARY KEY,
STATE_NAME VARCHAR(50),
STATE_CODE VARCHAR(5));

INSERT INTO USA_STATES(STATE_NAME,STATE_CODE)
VALUES('ALABAMA','AL'),
('ALASKA','AK'),
('ARIZONA','AZ'),
('ARKANSAS','AR'),
('CALIFORNIA','CA'),
('COLORADO','CA'),
('CONNECTICUT','CT'),
('DELAWARE','DE'),
('FLORIDA','FL'),
('GEOGIA','GA'),
('HAWAII','HI'),
('IDAHO','ID'),
('ILLINOIS','IL'),
('INDIANA','IN'),
('LOWA','IA'),
('KANSAS','KS'),
('KENTUCKY','KY'),
('LOUISIANA','LA'),
('MAINE','ME');

SELECT * FROM USA_STATES;

As we see in the above query, we have created a table as USA_STATES by using the CREATE TABLE statement and inserted some records into the table by using the INSERT INTO statement. Use the SELECT command to retrieve all records from the USA_STATES table.

MariaDB create index — MariaDB SELECT statement for USA_STATES table

The following is an example of a CREATE INDEX statement:

CREATE INDEX USA_STATENAME
ON USA_STATES(STATE_NAME);

EXPLAIN SELECT * FROM USA_STATES
WHERE STATE_ID>=20;

Following the execution of the CREATE INDEX command, MariaDB will construct a new data structure containing a copy of the values in the STATE_NAME column, as seen in the preceding query.

A balanced tree, often known as a B-tree, is the data structure utilized in the previous query. The USA_STATENAME index is structured in a sorted order that will be optimized to distribute information fast and efficiently.

In the EXPLAIN SELECT statement, the query just optimizes leverages the index, and examines all rows not a single row based on the above query.

MariaDB create index example — MariaDB CREATE INDEX statement Example

Also, read: MariaDB JSON Function + Examples

MariaDB index types

In MariaDB, there are four types of indexes:

Primary Key
Unique index
Plain index
Full-text index

Here we will use the key and index interchangeably.

1. Primary key

The MariaDB primary key must be unique and can not be null, every table must have only one primary key. The primary key is created when a new table is created using the CREATE TABLE statement.

Let’s create the new table as Country with the column id as a primary key.

CREATE TABLE Country(id INT PRIMARY KEY NOT NULL,country_name VARCHAR(25));

The above code contains the statement CREATE TABLE to create a new table as Country with columns id, country_name of type INT, and VARCHAR respectively.

While defining the column id type, It is also defined that the column id is the primary key using keyword PRIMARY KEY, and should not contain any null value using the keyword NOT NULL.

2. Unique index

The MariaDB unique index column must contain the unique value, this column can be null. The keyword UNIQUE KEY is used to make any column a unique column.

Let’s use the same table with an additional key as a unique key.

CREATE TABLE Country(id INT PRIMARY KEY NOT NULL,country_name VARCHAR(25), UNIQUE KEY ('country_name'));

In the above code, everything is the same but we have defined the new key, and that is the unique key for a column country_name. To add a unique key to the country_name column, the UNIQUE KEY (‘country_name’) keyword is used.

Insert the following records in a table Country.

INSERT INTO Country(id,country_name)VALUES(1,'United Kindom'),(2,'USA'),(3,'Canada');

The above query is used to insert the three values in a table Country like United Kindom, USA, Canada.

We could have also created the index on the country_name column using the keyword CREATE INDEX. The example is given below.

CREATE UNIQUE INDEX countryname ON index_table('country_name');

The full syntax to create an index using the CREATE INDEX is given below.

CREATE UNIQUE INDEX index_name ON table_name(column_name);

3. Plain Index

It doesn’t need to be a unique one. In MariaDB, the INDEX keyword is used to create a new index for any column in the MariaDB. Again create the same table with the plain index or key using the below query.

CREATE TABLE index_table(id INT NOT NULL,country_name VARCHAR(25), INDEX(id));

Here in the above code, look after the column, the index is defined using the INDEX(id) keyword.

4. Full-text index

It is a type of FULLTEXT and provides many options to search the part of the text from a field. It can be used for VARCHAR, CHAR, and TEXT in MariaDB.

full-text index can’t be created on partitioned tables. This FULL-TEXT index can be created with CREATE STATEMENT by providing its definition when a new table is created using that statement( CREATE STATEMENT).

The syntax to perform full-text searching is given below.

MATCH (col_name1,col_name2,...) AGAINST (expression (search_modifier))

The MATCH() accepts the column name with comma-separated each, AGAINST() accept the string to search, this string must be a literal string. search_modifier is used to specify the ‘what kind of search to perform.

Note: The FULL-TEXT index search works with MyISAM, Aria, InnoDB, and Mroonga engines.

Let’s create a table as Story_Line using the below code.

CREATE TABLE  Story_Line(story_line TEXT,FULLTEXT(story_line));

In the above code, we have defined the new table as Story_Line with columns story_line of type TEXT. Then making the column story_line as a full-text index using the keyword FULLTEXT(story_line).

Insert the following records.

INSERT INTO Story_Line(story_line) 
VALUES ('mariadbtips offers pratical solution to your problem'),
('mariadbtips provide only quality based services');

We are inserting the two texts in the column story_line.

Now search the whole paragraph with the word ‘conflict’ using the below code.

SELECT * FROM Story_Line WHERE MATCH(story_line) AGAINST('quality');

In the above code, look at the WHERE clause which contains the keyword MATCH() which contains the story_line column that needs to be searched. And the AGAINST() contains the word ‘quality’ that is searched in the column story_line.

After matching the word ‘quality’, it returns the whole paragraph in the above output.

Read How to Create Function in MariaDB

MariaDB Create Index Multiple Columns

In this subtopic, we will establish an index on many columns using the CREATE INDEX statement, which will be described with syntax and examples.

The following is the syntax for the CREATE INDEX statement on several columns:

SYNTAX:

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
  [ USING BTREE | HASH ]
  ON table_name
    (index_column_1 [(length)] [DESC | ASC], 
     index_column_2 [(length)] [DESC | ASC],
     ...
     index_column_n [(length)] [DESC | ASC]);

The following is an example of a CREATE INDEX statement on multiple columns:

EXAMPLE:

CREATE INDEX STATES_DETAIL
ON USA_STATES (STATE_ID, STATE_CODE);

EXPLAIN SELECT * FROM USD_STATES;

In the above query, we have created index_name as STATES_DETAIL from multiple columns as STATE_ID and STATE_CODE by using the CREATE INDEX statement. The query optimizer uses the index and checks only one row, not all rows, to produce the result, as seen in the output.

Even though an index can help a query run better, it comes at a price:

To maintain the index data structure up to date, you’ll need a lot of storage. MariaDB uses storage to store the sorted states detail column independently in the preceding example.
When the data in the indexed column changes, there is a write overhead. If you insert or edit values in the indexed columns, MariaDB must keep the data in the index up to date.

As a result, indexes can be created for columns that are commonly utilised in the selection criteria of SELECT queries.

Read: MariaDB vs Postgres – Detailed Comparison

MariaDB index json or json column

In MariaDB, the JSON can store the values like string, number, boolean, etc. It stores the values in the form of key pairs within curly braces. To create a column as JSON type, the JSON keyword is used.

Let’s create a table as Country_Capital, this table will store the country name with its capital.

CREATE TABLE Country_Capital(id INT,country_cap JSON, CHECK (JSON_VALID(country_cap)) );

Here we are creating the table as Country_Capital with columns id, country _cap of type INT, and JSON respectively. Then we have used the CHECK function to validate the JSON values while inserting the records using CHECK (JSON_VALID(country_cap)).

Insert the following records.

INSERT INTO Country_Capital(id,country_cap)VALUES(1,'{"USA":"Washington, D.C.","United Kindom":"London"}');

SELECT * FROM Country_Capital;

Using the above code, we are inserting the name of the country and its capital in the JSON format like {"USA":"Washington, D.C.","United Kindom":"London"}.

Read How to Create Trigger in MariaDB

MariaDB index column

We have learned the first method to create the index in MariaDB, now we will use the second method and that is the CREATE INDEX to create the new index in MariaDB.

The syntax is given below.

CREATE (UNIQUE | FULLTEXT | SPATIAL) INDEX index_name
  [ USING BTREE | HASH ]
  ON table_name
    (index_col1_name [(length)] [ASC | DESC]);

Unique: This keyword is used to make sure that the column must be unique and can be null.
Fulltext: To search the part of text from a field, this index provides the more options for that.
Spatial: It indexes the whole column and doesn’t allow any null value in the indexed column.
Index_name: The new name of the index that we create.
Table_name: The name of the table whose column is used for indexing.
Index_col1_name: Name of the column which is used for indexing.
Asc | Desc: The index is sorted in ascending and descending order.

Let’s create the table as countries that will contain the country name with a city.

CREATE TABLE Countries(id INT NOT NULL,country_name VARCHAR(25), city VARCHAR(20));

Above we have defined the new table as Countries with columns id, country_name, and city of type INT, VARCHAR(20), and VARCHAR(20) respectively.

Now create a new index as country_index on columns id and country_name using the below query.

CREATE INDEX country_index USING BTREE
  ON Countries (id,country_name);

To check the indexes for specific tables like countries use the below code.

SHOW INDEXES FROM countries;

The output shows the indexes for columns in the table Countries.

Read How to Drop Column from MariaDB Table

MariaDB Create Index If Not Exists

This section explains how to establish an index using the IF NOT EXISTS clause on the CREATE INDEX statement, including syntax and examples.

The IF NOT EXISTS clause’s purpose is to build index_name on the column_name, but if it already exists, it will produce a warning sign with a dialogue box. The following is the syntax of the CREATE INDEX statement with the IF NOT EXISTS clause:

SYNTAX:

CREATE INDEX INDEX_NAME 
IF NOT EXISTS 
ON TABLE_NAME (COLUMN_NAME);

Let’s have a look at the TAKEN table by using the SELECT statement:

SELECT * FROM TAKEN;

MariaDB create index if not exists — MariaDB SELECT statement for TAKEN table

The following is an example of how to create an index using the CREATE INDEX statement and the IF NOT EXISTS clause:

CREATE INDEX IF NOT EXISTS TAKENMOVIE
ON TAKEN (ACTOR_NAME);

EXPLAIN SELECT * FROM TAKEN 
WHERE ACTOR_NAME='LIAM NEESON';

In the first query, we created TAKENMOVIE as an index name from the TAKEN table on the ACTOR_NAME field, which is executed as a warning. So, when we use the EXPLAIN SELECT statement on the ACTOR_NAME column it gives two index names as TAKENMOVIE and ACTORNAME in the POSSIBLE_KEY column.

There is also another way to check the index_name of the column_name in the table by using the SHOW INDEX FROM TABLE_NAME then execute this query. The table_name from which we want to retrieve an index_name of that column_name.

MariaDB create index if not exists example — MariaDB Create Index If Not Exists Example

Read: MariaDB Variables Tutorial

MariaDB index hint

In MariaDB, we can use the hints to provide information about how to choose the index while processing the MariaDB queries. The syntax of index hints for a table is given below.

table_name [[as] alias] [index_hint_list]
index_hint_list:
 idx_hint ...
idx_hint:
 use {index|key}
 [{for {join|order by|group by}] ([idx_list])
 | ignore {index|key}
 [{for {join|order by|group by}] (idx_list)
 | force {index|key}
 [{for {join|order by|group by}] (idx_list)
idx_list:
 idx_name [, idx_name] ...

To search the rows in a table using the USE INDEX (idx_list), we instruct the server to use only the named indexes. Oppose to this, we use the IGNORE INDEX (idx_list) to instruct the database to doesn’t use some specific indexes.

We can check whether MariaDB is using the right indexes or not using the EXPLAIN keyword. if it uses the wrong indexes from the available indexes, then the above two hints are very useful to apply.

To search the rows in a table, if the given indexes don’t work, then we use the FORCE INDEX. The FORCE INDEX is similar to USE INDEX(idx_list).

The name of the indexes is provided to each hint, hint doesn’t use the column name. The index name of the PRIMARY KEY is PRIMARY.

Read How to Add Column in MariaDB

MariaDB index statistics

The SHOW INDEX_STATISTICS shows the statistics on the index, this command shows the usage of indexes and also locates the unused indexes.

It can also generate the command to remove the unused indexes.

SHOW INDEX_STATISTICS;

The code will show the table name with an index on that table.

But the above command is the old command to show index statistics, and this will work too. The new approach to show the index statistics is to fetch the statistics of the index using the table information_schema.INDEX_STATISTICS.

The syntax to show the statistics on the index is given below.

SELECT * FROM information_schema.INDEX_STATISTICS 
WHERE TABLE_NAME = "Country";

The above code fetches the statistics on the index of the table Country using the SELECT statement. Where in the WHERE clause the table name is filtered by providing the name of the table as TABLE_NAME = “Country”.

This is how to show the statistics on indexes.

MariaDB index on view

The index can’t be created on view because the view is a temporary table or not a real table in MariaDB. The view is aliases for a real relation or table.

Read How to Create Table in MariaDB

MariaDB Create index No Lock

In this section, we will create an index by utilizing the CREATE INDEX statement in the query with LOCK set to NONE, and we will show it with examples.

The syntax of the CREATE INDEX with LOCK set to NONE is as follows:

CREATE INDEX INDEX_NAME
ON TABLE_NAME (COLUMN_NAME)
LOCK [=] [DEFAULT|NONE|SHARED|EXCLUSIVE];

First, let’s have a look at the SKULLCANDY table by the following query:

SELECT * FROM SKULLCANDY;

The MariaDB SELECT statement retrieves all records from the SKULLCANDY table.

MariaDB create index no lock — MariaDB SELECT statement for SKULLCANDY table

Let’s have a look at the SKULLCANDY table by using the CREATE INDEX statement with LOCK as NONE in the query as shown below:

CREATE INDEX SKULLCANDY_LIFE
ON skullcandy ( NAME)
LOCK = NONE ;

EXPLAIN SELECT * FROM SKULLCANDY
WHERE NAME='TRUE Wireless EarBuds';

As seen in the preceding query, we have established an index_name of SKULLCANDY_LIFE on the NAME column of the SKULLCANDY table where there is no lock. The lock period can be DEFAULT, NONE, SHARED, and EXCLUSIVE.

After the lock has been finished, changing it will result in a warning and a syntax error, such as a duplicate index name.

MariaDB create index no lock example — MariaDB CREATE INDEX with NO LOCK Example

Read: MariaDB Drop Table + Examples

MariaDB index length and index size

The index length in MariaDB varies according to what kind of storage engine is used. the storage engine is a kind of small module that helps in reading, writing, and updating the data in the database.

The default storage engine in MariaDB is InnoDB, the other storage engine is XtraDB, Aria, and MyISAM.

The MyISAM has a maximum index length of 1000 bytes whereas InnoDB has 767 bytes. This index length changes according to engine to engine.

In the above paragraphs, we have learned “how index length changes in MariaDB”, let’s talk about index size.

The syntax to check the index size of all the tables in a database is given below.

SELECT table_schema AS database_name,
       TABLE_NAME,
       index_length AS index_size
       FROM information_schema.tables
       
WHERE table_type = 'BASE TABLE'
      AND table_schema NOT IN ('information_schema', 'sys',
                               'performance_schema', 'mysql')

ORDER BY index_size DESC;

All the information related to any table is stored in a table information_schema. So in the above code, the table_schema as database_name, TABLE_NAME, and index_length as index_size columns are fetched using the SELECT STATMENT.

Then filtering the information in WHERE clause by specifying that the table_type should be ‘BASE TABLE’. At last, all the columns are ordered by index_size in descending order.

The above code will show the information of all the tables with index_size of the current database. The output of the code is given below.

The above output shows the columns database_name, TABLE_NAME, and index_sizer from a table information_schema.tables.

MariaDB Create Index Using BTree

In this section, we will utilize the CREATE INDEX command to create a table index with the balanced tree or BTree index type, which will be explained in full with syntax and examples.

The syntax of the CREATE INDEX statement using BTREE is as follows:

SYNTAX:

CREATE INDEX INDEX_NAME
[USING BTREE | HASH]
ON TALE_NAME [COLUMN_NAME];

First, let’s look at the TAKEN table as returned by the following query:

SELECT * FROM TAKEN;

The MariaDB SELECT statement will retrieve all records from the TAKEN table.

MariaDB create index using btree — MariaDB SELECT statement for TAKEN table

An example of a BTree constructed with the CREATE INDEX statement is shown below:

EXAMPLE:

CREATE INDEX ACTORNAME
USING BTREE 
ON taken (ACTOR_NAME);

EXPLAIN SELECT * FROM taken 
WHERE ACTOR_NAME= 'LIAM NEESON';

In the above query, we have created index_name as ACTORNAME by using BTREE on the TAKEN table for the ACTOR_NAME column. And we have explained the indexes by using the EXPLAIN SELECT statement on the TAKEN table based on the WHERE condition where the ACTOR_NAME column is LIAM NEESON.