MariaDB Index

Posted on In

Indexes are used to make data retrieval faster.

What is Index

From Wikipedia: A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.

The structure of an index is usually a b-tree or a hash table. Creating an index requires additional disk space. Two many indexes could exceed file system size limit. So be careful when creating indexes.

Creating Index

A Unique Index must be unique, but it can be null. Duplicate values are not allowed

creates an index using customer’s home_phone field. The field should already be UNIQUE before creating unique index

1
CREATE UNIQUE INDEX idx_email ON customer(email);

Create an index using customer’s first name and last name field. It is not a unique index so duplicate values are allowed here.

1
CREATE INDEX idx_name ON customer(first_name, last_name);

Fulltext index

A full-text index in MariaDB is an index of type FULLTEXT, and it allows more options when searching for portions of text from a field.
Full-text searching is performed using MATCH() ... AGAINST syntax.

Example to create a fulltext index.

1
create fulltext index idx_description on product(description);

To use MATCH() ... AGAINST syntax syntax

1
SELECT * from product WHERE MATCH(description) AGAINST('phone');

Drop index

1
2
ALTER TABLE customer
DROP INDEX idx_email;

Reference