totn MariaDB

MariaDB: Indexes

This MariaDB tutorial explains how to create, drop, and rename indexes in MariaDB with syntax and examples.

What is an Index in MariaDB?

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns.

Create an Index

There are 2 ways to create an index. You can either create an index when you first create a table using the CREATE TABLE statement or you can use the CREATE INDEX statement after the table has been created.

Syntax

The syntax to create an index using the CREATE TABLE statement in MariaDB is:

CREATE TABLE table_name
( 
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
  column_n datatype [ NULL | NOT NULL ],

  INDEX index_name [ USING BTREE | HASH ]
    (index_col1 [(length)] [ASC | DESC], 
     index_col2 [(length)] [ASC | DESC],
     ...
     index_col_n [(length)] [ASC | DESC])
);

OR

The syntax to create an index using the CREATE INDEX statement in MariaDB is:

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
  [ USING BTREE | HASH ]
  ON table_name
    (index_col1 [(length)] [ASC | DESC], 
     index_col2 [(length)] [ASC | DESC],
     ...
     index_col_n [(length)] [ASC | DESC]);
UNIQUE
Optional. The UNIQUE modifier indicates that the combination of values in the indexed columns must be unique.
FULLTEXT
Optional. The FULLTEXT modifier indexes the entire column and does not allow prefixing. InnoDB and MyISAM tables support this option.
SPATIAL
Optional. The SPATIAL modifier indexes the entire column and does not allow indexed columns to contain NULL values. InnoDB (starting in MariaDB 5.7) and MyISAM tables support this option.
index_name
The name to assign to the index.
table_name
The name of the table in which to create the index.
index_col1, index_col2, ... index_col_n
The columns to use in the index.
length
Optional. If specified, only a prefix of the column is indexed not the entire column. For non-binary string columns, this value is the given number of characters of the column to index. For binary string columns, this value is the given number of bytes of the column to index.
ASC
Optional. The index is sorted in ascending order for that column.
DESC
Optional. The index is sorted in descending order for that column.

Example

Let's look at an example of how to create an index in MariaDB using the CREATE TABLE statement. This statement would both create the table as well as the index at the same time.

For example:

CREATE TABLE websites
( website_id INT(11) NOT NULL AUTO_INCREMENT,
  website_name VARCHAR(25) NOT NULL,
  server_name VARCHAR(20),
  creation_date DATE,
  CONSTRAINT websites_pk PRIMARY KEY (website_id),
  INDEX websites_idx (website_name)
);

In this example, we've created the websites table as well as an index called websites_idx which consists of the website_name column.

Next, we will show you how to create the table first and then create the index using the CREATE INDEX statement.

For example:

CREATE TABLE websites
( website_id INT(11) NOT NULL AUTO_INCREMENT,
  website_name VARCHAR(25) NOT NULL,
  server_name VARCHAR(20),
  creation_date DATE,
  CONSTRAINT websites_pk PRIMARY KEY (website_id)
);

CREATE INDEX websites_idx
  ON websites (website_name);

In this example, the CREATE TABLE statement would create the websites table. The CREATE INDEX statement would create an index called websites_idx that consists of the website_name field.

Unique Index

To create a unique index on a table, you need to specify the UNIQUE keyword when creating the index. Again, this can be done with either a CREATE TABLE statement or a CREATE INDEX statement.

For example:

CREATE TABLE websites
( website_id INT(11) NOT NULL AUTO_INCREMENT,
  website_name VARCHAR(25) NOT NULL,
  server_name VARCHAR(20),
  creation_date DATE,
  CONSTRAINT websites_pk PRIMARY KEY (website_id),
  UNIQUE INDEX websites_idx (website_name, server_name)
);

OR

CREATE TABLE websites
( website_id INT(11) NOT NULL AUTO_INCREMENT,
  website_name VARCHAR(25) NOT NULL,
  server_name VARCHAR(20),
  creation_date DATE,
  CONSTRAINT websites_pk PRIMARY KEY (website_id)
);

CREATE UNIQUE INDEX websites_idx
  ON websites (website_name, server_name);

Both of these examples would create a unique index on the website_name and server_name fields so that the combination of these fields must always contain a unique value with no duplicates. This is a great way to enforce integrity within your database if you require unique values in columns that are not part of your primary key.

Drop an Index

You can drop an index in MariaDB using the DROP INDEX statement.

Syntax

The syntax to drop an index using the DROP INDEX statement in MariaDB is:

DROP INDEX index_name
  ON table_name;
index_name
The name of the index to drop.
table_name
The name of the table where the index was created.

Example

Let's look at an example of how to drop an index in MariaDB.

For example:

DROP INDEX websites_idx
  ON websites;

In this example, we've dropped an index called websites_idx from the websites table.

Rename an Index

You can rename an index in MariaDB using the ALTER TABLE statement.

Syntax

The syntax to rename an index using the ALTER TABLE statement is:

ALTER TABLE table_name
  DROP INDEX index_name,
  ADD INDEX new_index_name [ USING BTREE | HASH ]
    (index_col1 [(length)] [ASC | DESC], 
     index_col2 [(length)] [ASC | DESC],
     ...
     index_col_n [(length)] [ASC | DESC]);
table_name
The name of the table where the index was created.
index_name
The name of the index that you wish to rename.
new_index_name
The new name for the index.

Example

Let's look at an example of how to rename an index in MariaDB.

For example:

ALTER TABLE websites
  DROP INDEX websites_idx,
  ADD INDEX websites_new_index (website_name, server_name);

In this example, we've renamed the index called websites_idx to websites_new_index. This was done by dropping the old index and then adding the new index.

Share on: