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 and there are no UNIQUE indexes, 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);

The fact that a UNIQUE constraint can be NULL is often overlooked. In SQL any NULL is never equal to anything, not even to another NULL. Consequently, a UNIQUE constraint will not prevent one from storing duplicate rows if they contain null values:

CREATE TABLE Table_1 (a int not null, b int, unique (a,b));
insert into Table_1 values (1,1),(2,NULL),(2,NULL);
select * from Table_1;

+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 2 | NULL |
| 2 | NULL |
+---+------+

Indeed, in SQL two last rows, even if identical, are not equal to each other:

SELECT (2, NULL) = (2, NULL);

+---------------------- +
| (2, NULL) = (2, NULL) |
+---------------------- +
| 0                     |
+---------------------- +

In MariaDB you can combine this with virtual columns to enforce uniqueness over a subset of rows in a table:

create table Table_1 (
  user_name varchar(10),
  status enum('Active', 'On-Hold', 'Deleted'),
  del char(0) as (if(status in ('Active', 'On-Hold'),'', NULL)) persistent,
  unique(user_name,del)
)

This table structure ensures that all active or on-hold users have distinct names, but as soon as a user is deleted, his name is no longer part of the uniqueness constraint, and another user may get the same name.

If a unique index consists of a column where trailing pad characters are stripped or ignored, inserts into that column where values differ only by the number of trailing pad characters will result in a duplicate-key error.

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.

If you want to know how to re-create an index, run SHOW CREATE TABLE.

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.

If user statistics are enabled, the Information Schema INDEX_STATISTICS table stores the index usage.

If the slow query log is enabled and the log_queries_not_using_indexes server system variable is ON, the queries which do not use indexes are logged.

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...
Loading