Indexes

SQL theory states that a key is a data constraint, for example a unique key or foreign key, it also provides access to a limited set of data more quickly. Constraints can be applied to a single data field or to more than one data field.

simple key a key constraint or index applied to one data field
composite key a key constraint or index applied to more than one data field
surrogate key a unique key that is a meaningless number, normally a ID key

There are a number of different types of constraints and index which I will discuss throughout this section

constraints UNIQUE KEY, PRIMARY KEY and FOREIGN KEY
indexes INDEX, FULLTEXT INDEX, SPATIAL INDEX

you also have UNIQUE KEY and PRIMARY KEY which are also constraints

To constraint data we use the UNIQUE KEY, PRIMARY KEY and FOREIGN KEY, data is check during an insert or an update. unique keys and primary keys protect against duplicating, the difference being that you can have only one primary key and NULL's are not allowed for a primary key. A foreign key is like a check where the data must be in another table, there is no check constraint in MySQL.

creating constraints alter table tablename add unique key key_name (col1,col2..)
alter table tablename add primary key key_name (column)

create unique index index_name on tablename (col1,col2..)

Note: you can create constraints when creating the table which is known as in-line - see below examples
dropping constraints drop index index_name on tablename
alter table tablename drop foreign key key_name
constraints examples

## creating the constraints all on one line (known as in-line)
  create table test_cons (
  rowid int PRIMARY KEY AUTO_INCREMENT,
  uniqkey1 int UNIQUE KEY
);

## Now lets use the out-of-line example to create the constraints and give them meaningful names

create table other_table (
  other_column int primary key
);

create table test_cons2 (
  rowid int,
  uniqkey1 int,
  uniqkey2 int,
  uniqkey3 int,
  foreign_id int,
  PRIMARY KEY (rowid),
  constraint UNIQUE KEY simple_key (uniqkey1),
  constraint UNIQUE KEY composite_key (uniqkey2,uniqkey3),
  constraint fk_foreign_id FOREIGN KEY (foreign_id) REFERENCES other_table(other_column)
);

## Seeing the results, first a quick look at the table, notice the key column

mysql> desc test_cons2;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| rowid      | int(11) | NO   | PRI | 0       |       |
| uniqkey1   | int(11) | YES  | UNI | NULL    |       |
| uniqkey2   | int(11) | YES  | MUL | NULL    |       |
| uniqkey3   | int(11) | YES  |     | NULL    |       |
| foreign_id | int(11) | YES  | MUL | NULL    |       |
+------------+---------+------+-----+---------+-------+
5 rows in set (0.00 sec)

## Here you can see in detail the key names what columns the indexes are on and sequence number of the key (if you have a composite key)
## also you can see the type of index, if I had any data in the table I would be able to see the cardinality

mysql> show indexes from test_cons2;
+------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| Table      | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type |
+------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| test_cons2 | 0          | PRIMARY       | 1            | rowid       | A         | 0           | NULL     | NULL   |      | BTREE      |
| test_cons2 | 0          | simple_key    | 1            | uniqkey1    | A         | 0           | NULL     | NULL   | YES  | BTREE      |
| test_cons2 | 0          | composite_key | 1            | uniqkey2    | A         | 0           | NULL     | NULL   | YES  | BTREE      |
| test_cons2 | 0          | composite_key | 2            | uniqkey3    | A         | 0           | NULL     | NULL   | YES  | BTREE      |
| test_cons2 | 1          | fk_foreign_id | 1            | foreign_id  | A         | 0           | NULL     | NULL   | YES  | BTREE      |
+------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
5 rows in set (0.00 sec)

Speed

Indexes are data structures, they can either be B-tree, R-tree (only available for indexes on SPATIAL data) or hash data structures. B-tree's are the most common and is available for all storage engines (except NDB), they are excellent for finding data ranges, hash data structures are for finding exact matches or a series of exact matches.

You generally create indexes on the most common fields of a table that are used, if no index exists a full table scan is performed which can be an expensive operation in terms of I/O, using an index reduces the amount of data that has to be search and thus reduces the I/O operation. Think of it in terms of a reading book it's easy to lookup something in the index and find the page number than to search through the whole the book trying to find what you are looking for, hence why lots of books have indexes at the back.

Data constraints are particularly good candidates for indexes, they enable you to perform very quick lookups on data. FULLTEXT INDEX and SPATIAL INDEX create special data structures designed to easily perform text matching and spatial data searching.

Indexes are always kept current in MySQL, when an update, insert or delete occurs within the table the index must change too, so if your table has many writes creating an index may reduce performance, this is the tradeoff for faster data lookups is slower data updates, in other words if the table has lots of write keep the indexes to a minimum. Also check that columns don't have two indexes on the same information, remember both indexes will have to be maintained.

Indexes

The MySQL optimizer uses the leftmost prefix when it deciding to use an index, what this means is that if you create a composite key your query should contain the leftmost column of the index otherwise the index would not be used, for example lets say you create a composite key with the columns (A,B,C) the optimizer would use the index where the query where clause would have the following

The index would not be used if you query only contained the following in the where clause

Just remember this when creating composite keys, Oracle does have a feature called index skip-scan which can use the rightmost keys.

I have already created some constraints above which as you know will create indexes, now lets create some indexes just for data retrieval performance, in other words that are not for data integrity.

creating

create index employee_idx on employees (fname, lname);

alter table add index employee_idx on employees (fname, lname);

## if creating a blob or text data type you need to specify a prefix
create index employee_idx on employees (lname(40));

## specifying different index types
create index employee_idx on employees (lname) using BTREE;
create index employee_idx on employees (lname) using HASH;

Note: when using the create statement a index name is required, however when using the alter table to create an index no index name is required and thus the server will automatically create one for you.

dropping drop index employee_idx on employees;

alter table drop index employee_idx on employees;
displaying show indexes from employees;

A point to remember is that if you using the alter table to create an index it will obtain a write-lock on the entire table, making it unable to be updated for the duration of the alter table statement, this could be a long time depending on the size of the index being created.

All Btree indexes are stored in ascending order only, numbers are stored in numerical order and strings are stored in lexical order according to the string collation being used. The ordering of an index is critical because it is optimized for scanning sequentially, it can only be used to find the next value not the previous value. It is possible to use the DESC keyword when creating an index but it will be ignored and the index created in ascending order. String indexes allow you specify a length for the index value, this is known as an index prefix and may be used with any string type, it is required for blob or text data types.

Foreign Key Constraints

You change change the behavior on the referenced foreign table (parent) if an update or delete statement tries to change the existing data on the parent table, the server can cascade the change to the corresponding records in the referencing (child) table, or the server can reject the change.

Both on update and on delete are optional, if no option is given then restrict is the default. When referencing a foreign key that key must have an index build on it other you will receive an error, they also must have similar data types. The following requirements are require to create a foreign key

For examples on creating a foreign see above in the constraint examples

Index Maintenance

There is a limited number of tasks that you can perform on an indexes in MySQL, most of the index maintenance tasks are normally performed on the table itself see Table Maintenance

disable/enable keys alter table tablename disable keys;
alter table tablename enable keys;