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.

 

MariaDB: Unique Constraints

This MariaDB tutorial explains how to create, add, and drop unique constraints in MariaDB with syntax and examples.

What is a unique constraint in MariaDB?

A unique constraint is a single field or combination of fields that uniquely defines a record. Some of the fields can contain null values as long as the combination of values is unique.

What is the difference between a unique constraint and a primary key?

Primary Key	Unique Constraint
None of the fields that are part of the primary key can contain a null value.	Some of the fields that are part of the unique constraint can contain null values as long as the combination of values is unique.

Create unique Contraint - Using a CREATE TABLE statement

The syntax for creating a unique constraint using a CREATE TABLE statement in MariaDB is:

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

  CONSTRAINT constraint_name UNIQUE (uc_col1, uc_col2, ... uc_col_n)
);

table_name

The name of the table that you wish to create.

column1, column2

The columns that you wish to create in the table.

constraint_name

The name of the unique constraint.

uc_col1, uc_col2, ... uc_col_n

The columns that make up the unique constraint.

Example

Let's look at an example of how to create a unique constraint in MariaDB using the CREATE TABLE statement.

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

In this example, we've created a unique constraint on the websites table called websites_unique. It consists of only one field - the website_name field.

We could also create a unique constraint with more than one field as in the example below:

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

Create unique contraint - Using an ALTER TABLE statement

The syntax for creating a unique constraint using an ALTER TABLE statement in MariaDB is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);

table_name

The name of the table to modify. This is the table that you wish to add a unique constraint to.

constraint_name

The name of the unique constraint.

column1, column2, ... column_n

The columns that make up the unique constraint.

Example

Let's look at an example of how to add a unique constraint to an existing table in MariaDB using the ALTER TABLE statement.

ALTER TABLE websites
ADD CONSTRAINT websites_unique UNIQUE (website_name);

In this example, we've created a unique constraint on the existing websites table called websites_unique. It consists of the field called website_name.

We could also create a unique constraint with more than one field as in the example below:

ALTER TABLE websites
ADD CONSTRAINT website_server_unique UNIQUE (website_name, server_name);

Drop Unique Constraint

The syntax for dropping a unique constraint in MariaDB is:

ALTER TABLE table_name
DROP INDEX constraint_name;

table_name

The name of the table to modify. This is the table that you wish to remove the unique constraint from.

constraint_name

The name of the unique constraint to remove.

Example

Let's look at an example of how to remove a unique constraint from a table in MariaDB.

ALTER TABLE websites
DROP INDEX websites_unique;

In this example, we're dropping a unique constraint on the websites table called websites_unique.

 

MariaDB: Primary Keys

This MariaDB tutorial explains how to create and drop a primary key in MariaDB with syntax and examples.

What is a primary key in MariaDB?

In MariaDB, a primary key is a single field or combination of fields that uniquely defines a record. None of the fields that are part of the primary key can contain a NULL value. A table can have only one primary key.

A primary key is created in MariaDB using either a CREATE TABLE statement or an ALTER TABLE statement.

Create Primary Key - Using CREATE TABLE statement

You can create a primary key in MariaDB with the CREATE TABLE statement.

Syntax

The syntax to create a primary key using the CREATE TABLE statement in MariaDB is:

CREATE TABLE table_name
(
  column1 column_definition,
  column2 column_definition,
  ...

  CONSTRAINT [constraint_name] 
   PRIMARY KEY [ USING BTREE | HASH ] (column1, column2, ... column_n)
);

table_name

The name of the table that you wish to create.

column1, column2

The columns that you wish to create in the table. See the MariaDB CREATE TABLE statement for more detailed CREATE TABLE syntax as this is an over-simplification to demonstrate how to create a Primary Key.

constraint_name

The name of the primary key.

column1, column2, ... column_n

The columns that make up the primary key.

Example

Let's look at an example of how to create a primary key using the CREATE TABLE statement in MariaDB.

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)
);

In this example, we've created a primary key on the websites table called websites_pk. It consists of only one column - the website_id column.

We could also create a primary key with more than one field as in the example below:

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

This example creates a primary key called websites_pk that is made up of a combination of the website_name and server_name columns. So each combination of website_name and server_name must be unique in the websites table.

Create Primary Key - Using ALTER TABLE statement

You can create a primary key in MariaDB with the ALTER TABLE statement.

Syntax

The syntax to create a primary key using the ALTER TABLE statement in MariaDB is:

ALTER TABLE table_name
  ADD CONSTRAINT [ constraint_name ]
    PRIMARY KEY [ USING BTREE | HASH ] (column1, column2, ... column_n)

table_name

The name of the table to modify.

constraint_name

The name of the primary key.

column1, column2, ... column_n

The columns that make up the primary key.

Example

Let's look at an example of how to create a primary key using the ALTER TABLE statement in MariaDB.

ALTER TABLE websites
  ADD CONSTRAINT websites_pk
    PRIMARY KEY (website_id);

In this example, we've created a primary key on the existing websites table called websites_pk. It consists of the website_id column.

We could also create a primary key with more than one field as in the example below:

ALTER TABLE websites
  ADD CONSTRAINT websites_pk
    PRIMARY KEY (website_name, server_name);

This example we've created a primary key called websites_pk that is made up of a combination of the website_name and server_name columns.

Drop Primary Key

You can drop a primary key in MariaDB using the ALTER TABLE statement.

Syntax

The syntax to drop a primary key in MariaDB is:

ALTER TABLE table_name
  DROP PRIMARY KEY;

table_name

The name of the table to modify.

Example

Let's look at an example of how to drop a primary key using the ALTER TABLE statement in MariaDB.

ALTER TABLE websites
  DROP PRIMARY KEY;

In this example, we've dropped the primary key on the websites table. We do not need to specify the name of the primary key as there can only be one on a table.

 

MariaDB: SELECT Statement

This MariaDB tutorial explains how to use the MariaDB SELECT statement with syntax and examples.

Description

The MariaDB SELECT statement is used to retrieve records from one or more tables in MariaDB.

Syntax

In its simplest form, the syntax for the SELECT statement in MariaDB is:

SELECT expressions
FROM tables
[WHERE conditions];

However, the full syntax for the MariaDB SELECT statement is:

SELECT [ ALL | DISTINCT ]
expressions
FROM tables
[WHERE conditions]
[GROUP BY expressions]
[HAVING condition]
[ORDER BY expression [ ASC | DESC ]]
[LIMIT [offset_value] number_rows | LIMIT number_rows OFFSET offset_value]
[PROCEDURE procedure_name]
[INTO [ OUTFILE 'file_name' options 
       | DUMPFILE 'file_name'
       | @variable1, @variable2, ... @variable_n ]
[FOR UPDATE | LOCK IN SHARE MODE];

Parameters or Arguments

ALL

Optional. ALL returns all matching rows.

DISTINCT

Optional. DISTINCT removes duplicates from the result set.

expressions

The columns or calculations that you wish to retrieve. Use * if you wish to select all columns.

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

WHERE conditions

Optional. The conditions that must be met for the records to be selected.

GROUP BY expressions

Optional. It collects data across multiple records and groups the results by one or more columns.

HAVING condition

Optional. It is used in combination with the GROUP BY to restrict the groups of returned rows to only those whose the condition is TRUE.

ORDER BY expression

Optional. It is used to sort the records in your result set.

LIMIT

Optional. If LIMIT is provided, it controls the maximum number of records to retrieve. At most, the number of records specified by number_rows will be returned in the result set. The first row returned by LIMIT will be determined by offset_value.

PROCEDURE

Optional. If provided, procedure_name is the name of the procedure that should process the data in the result set.

INTO

Optional. If provided, it allows you to write the result set to either a file or variable.

Value	Explanation
INTO OUTFILE 'filename' options	Writes the result set to a file called filename on the server host. For options, you can specify: FIELDS ESCAPED BY 'character' FIELDS TERMINATED BY 'character' [ OPTIONALLY ENCLOSED BY 'character' ] LINES TERMINATED BY 'character' where character is the character to display as the ESCAPE, ENCLOSED, or TERMINATED character. For example: SELECT supplier_id, supplier_name FROM suppliers INTO OUTFILE 'results.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
INTO DUMPFILE 'filename'	Writes one row of the result set to a file called filename on the server host. With this method, there is no column termination, no line termination, or escape processing.
INTO @variable1, @variable2, ... @variable_n	Writes the result set to one or more variables, as specified by @variable1, @variable2, ... @variable_n

FOR UPDATE

Optional. Records affected by the query are write-locked until the transaction has completed

LOCK IN SHARE MODE

Optional. Records affected by the query can be used by other transactions but can not be updated or deleted by those other transactions.

Example - Select all columns from one table

Let's look at how to use a MariaDB SELECT query to select all columns from a table.

For example:

SELECT *
FROM sites
WHERE site_name = 'TechOnTheNet.com'
ORDER BY site_id ASC;

In this SELECT example, we've used * to signify that we wish to select all fields from the sites table where the site_name is 'TechOnTheNet.com'. The results are sorted by site_id in ascending order.

Example - Select individual columns from one table

When using the SELECT statement in MariaDB, you do not have to select all columns from the table. Instead, you can select the individual columns that you would like to return in your result set.

For example:

SELECT site_id, site_name
FROM sites
WHERE site_id < 32
ORDER BY site_id ASC, site_name DESC;

This MariaDB SELECT example would return only the site_id and site_name fields from the sites table where the site_id is less than 32. The results are sorted by site_id in ascending order and then site_name in descending order.

Example - Select columns from multiple tables

The SELECT statement in MariaDB can also select columns from more than one table.

For example:

SELECT pages.page_id, sites.site_name
FROM sites
INNER JOIN pages
ON sites.site_id = pages.site_id
WHERE sites.site_name = 'TechOnTheNet.com'
ORDER BY pages.page_id;

This SELECT statement example joins two tables to return a result set that includes the page_id and site_name fields. The results of the SELECT statement are filtered where the site_name is 'TechOnTheNet.com' and the site_id value matches in both the sites and pages table. The results are sorted by page_id in ascending order.

Example - write to a file

Finally, let's look at how to use the MariaDB SELECT statement to write the results of the SELECT statement to a file.

For example:

SELECT site_id, site_name
FROM sites
WHERE site_name = 'google.com'
ORDER BY site_id DESC
INTO OUTFILE 'results.txt'
     FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
     LINES TERMINATED BY '\n';

This MariaDB SELECT example would return only the site_id and site_name fields from the sites table where the site_name is 'google.com'. The results would be sorted by site_id in descending order and written to a file called results.txt.

 

MariaDB: Functions - Listed Alphabetically

For easy reference, we have provided a list of all MariaDB functions. The list of MariaDB functions is sorted alphabetically based on the function name.

These functions can be used in SQL statements or queries in MariaDB. Or, they can be used within the programming environment provided by the MariaDB database, such as stored procedures, functions, triggers, etc.

Below is the list of MariaDB functions, sorted by alphabetically by function name.

ABSACOSADDDATEADDTIMEASCIIASINATANATAN2AVGBINBINARYCASECASTCEILCEILINGCHAR_LENGTHCHARACTER_LENGTHCOALESCECONCATCONCAT_WSCONNECTION_IDCONVCONVERTCOSCOTCOUNTCURDATECURRENT_DATECURRENT_TIMECURRENT_TIMESTAMPCURRENT_USERCURTIMEDATABASEDATEDATE_ADDDATE_FORMATDATE_SUBDATEDIFFDAYDAYNAMEDAYOFMONTHDAYOFWEEKDAYOFYEARDEGREESDIVENCRYPTEXPEXTRACTFIELD

FIND_IN_SETFLOORFORMATFROM_DAYSGREATESTHOURIFIFNULLINSERTINSTRISNULLLAST_DAYLAST_INSERT_IDLCASELEASTLEFTLENGTHLNLOCALTIMELOCALTIMESTAMPLOCATELOGLOG10LOG2LOWERLPADLTRIMMAKEDATEMAKETIMEMAXMD5MICROSECONDMIDMINMINUTEMODMONTHMONTHNAMENOWNULLIFOLD_PASSWORDPASSWORDPERIOD_ADDPERIOD_DIFFPIPOSITIONPOWPOWERQUARTER

RADIANSRANDREGEXPREGEXP_INSTRREGEXP_REPLACEREGEXP_SUBSTRREPEATREPLACEREVERSERIGHTROUNDRPADRTRIMSEC_TO_TIMESECONDSESSION_USERSIGNSINSPACESQRTSTR_TO_DATESTRCMPSUBDATESUBSTRSUBSTRINGSUBSTRING_INDEXSUBTIMESUMSYSDATESYSTEM_USERTANTIMETIME_FORMATTIME_TO_SECTIMEDIFFTIMESTAMPTO_DAYSTRIMTRUNCATEUCASEUPPERUSERVERSIONWEEKWEEKDAYWEEKOFYEARYEARYEARWEEK