MySQL indexes

From Cosmin's Wiki

Jump to: navigation, search

Home > MySQL > MySQL indexes



Contents

Brief Description

Indexes are used if you need to quickly find certain rows with specific column values in a table. If you don't use an index, MySQL will scan the complete table until it finds the row you need (full table scan). If however your table has an index for the given column, MySQL will be able to quickly find that row without having to look in the whole table. If a table has 1,000 rows, this is at least 100 times faster than reading sequentially. If you need to access most of the rows, it is faster to read sequentially, because this minimizes disk seeks.

Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions are that indexes on spatial data types use R-trees, and that MEMORY tables also support hash indexes.

You would mainly want to use indexes on a table in the following situations:

  • To find the rows matching a WHERE clause quickly.
  • To retrieve rows from other tables when performing joins
  • To find the MIN() or MAX() value for a specific indexed column
  • To sort or group a table if the sorting or grouping is done on a leftmost prefix of a usable key (for example, ORDER BY key_part1, key_part2). If all key parts are followed by DESC, the key is read in reverse order.


Creating Indexes

The general syntax for creating an index in MySQL is:

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_type]
 
index_col_name:
    col_name [(length)] [ASC | DESC]
 
index_type:
    USING {BTREE | HASH | RTREE}
 

CREATE INDEX is mapped to an ALTER TABLE statement to create indexes.

CREATE INDEX cannot be used to create a PRIMARY KEY; use ALTER TABLE instead. CREATE INDEX enables you to add indexes to existing tables, although normally you would create the indexes at the moment you create the table with CREATE TABLE.

Indexes can be created that use only the leading part of column values, using col_name(length) syntax to specify an index prefix length:

  • Prefixes can be specified for CHAR, VARCHAR, BINARY, and VARBINARY columns.
  • BLOB and TEXT columns also can be indexed, but a prefix length must be given.
  • Prefix lengths are given in characters for non-binary string types and in bytes for binary string types. That is, index entries consist of the first length characters of each column value for CHAR, VARCHAR, and TEXT columns, and the first length bytes of each column value for BINARY, VARBINARY, and BLOB columns.

The statement shown here creates an index using the first 10 characters of the name column in the customer table:

CREATE INDEX idx_partial ON customer (name(10));

Trying to create a unique index on a table which already contains duplicate rows for the columns in the unique index like this:

CREATE UNIQUE INDEX idx_1 ON TEST1 (name);

will generate an error like:

ERROR 1062 (23000): Duplicate entry 'cucu' for key 2

Should you still want to add the unique index, ignoring the already existing rows in the table, you need to go like this:

ALTER IGNORE TABLE TEST1 ADD UNIQUE INDEX idx_2 (name);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 2  Warnings: 0


Dropping Indexes

Dropping an index on a table is done like this:

DROP INDEX index_name ON tbl_name