Getting Started with Indexes

You are viewing an old version of this article. View the current version here.

There are four main kinds of indexes; primary keys, unique indexes, plain indexes and full-text indexes.

The terms 'KEY' and 'INDEX' are generally used interchangeably, and statements should work with either keyword.

Primary Key

A primary key is unique and can never be null. It will always identify only one record, and each record must be represented. Each table can only have one primary key.

In XtraDB/InnoDB tables, all indexes contain the primary key as a suffix. Thus, when using this storage engine, keeping the primary key as small as possible is particularly important. If a primary key does not exist, InnoDB creates a 6-bytes clustered index which is invisible to the user.

Many tables use a numeric ID field as a primary key.

Primary keys are usually added when the table is created with the CREATE TABLE statement. For example, the following creates a primary key on the ID field. Note that the ID field had to be defined as NOT NULL, otherwise the index could not have been created.

CREATE TABLE `Employees` (
  `ID` TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  `First_Name` VARCHAR(25) NOT NULL,
  `Last_Name` VARCHAR(25) NOT NULL,
  `Position` VARCHAR(25) NOT NULL,
  `Home_Address` VARCHAR(50) NOT NULL,
  `Home_Phone` VARCHAR(12) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=Aria;

You cannot create a primary key with the CREATE INDEX command. If you do want to add one after the table has already been created, use ALTER TABLE, for example:

ALTER TABLE Employees ADD PRIMARY KEY(ID);

Unique Index

A Unique Index must be unique, but it can be null. So each key value indentifies only one record, but not each record needs to be represented.

For example, to create a unique key on the Employee_Code field, as well as a primary key, use:

CREATE TABLE `Employees` (
  `ID` TINYINT(3) UNSIGNED NOT NULL,
  `First_Name` VARCHAR(25) NOT NULL,
  `Last_Name` VARCHAR(25) NOT NULL,
  `Position` VARCHAR(25) NOT NULL,
  `Home_Address` VARCHAR(50) NOT NULL,
  `Home_Phone` VARCHAR(12) NOT NULL,
  `Employee_Code` VARCHAR(25) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY (`Employee_Code`)
) ENGINE=Aria;

Unique keys can also be added after the table is created with the CREATE INDEX command, or with the ALTER TABLE command, for example:

ALTER TABLE Employees ADD UNIQUE `EmpCode`(`Employee_Code`); 

and

CREATE UNIQUE INDEX HomePhone ON Employees(Home_Phone);

Multiple-column Indexes

Indexes can contain more than one column. MariaDB is able to use one or more columns on the leftmost part of the index, if it cannot use the whole index.

Choosing Indexes

In general you should only add indexes to match the queries your application uses. Any extra will waste resources. In an application with very small tables, indexes will not make much difference but as soon as your tables are larger than your buffer sizes the indexes will start to speed things up dramatically.

Using the EXPLAIN statement on your queries can help you decide which columns need indexing.

If you query contains something like LIKE '%word%', without a fulltext index you are using a full table scan every time, which is very slow.

If your table has a large number of reads and writes, consider using delayed writes. This uses the db engine in a "batch" write mode, which cuts down on disk io, therefore increasing performance.

Use the CREATE INDEX command to create an index.

If you are building a large table then for best performance add the index after the table is populated with data. This is to increase the insert performance and remove the index overhead during inserts.

Viewing indexes

You can view which indexes are present on a table, as well as details about them, with the SHOW INDEX statement.

When to remove an index

If an index is rarely used (or not used at all) then remove it to increase INSERT, and UPDATE performance.

The initial version of this article was copied, with permission, from http://hashmysql.org/wiki/Proper_Indexing_Strategy on 2012-10-30.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.