Creating Indexes

CREATE INDEX, ALTER TABLE ADD INDEX and an inline table key are three spellings of the same operation.

Syntax

CREATE [UNIQUE] INDEX index_name ON tbl_name ({col_name [(length)] | (expr)} [ASC | DESC], ...) [ALGORITHM = {DEFAULT | INPLACE | COPY}] [LOCK = {DEFAULT | NONE | SHARED | EXCLUSIVE}]

An index can be declared inside CREATE TABLE, added with ALTER TABLE ... ADD INDEX, or added with CREATE INDEX. These are not three features. CREATE INDEX is shorthand that MySQL maps onto an ALTER TABLE statement, so these two lines do exactly the same thing:

CREATE INDEX idx_customer ON orders (customer_id);
ALTER TABLE orders ADD INDEX idx_customer (customer_id);

Inside a table definition, KEY is a synonym for INDEX, and UNIQUE KEY for UNIQUE INDEX. Recognise all of them; they produce identical objects.

Key parts

Each key part is col_name [(length)] [ASC | DESC] or a parenthesised expression. A prefix length indexes the first N characters of a long string — mandatory for BLOB and TEXT under InnoDB, which caps an index key prefix at 3072 bytes for DYNAMIC and COMPRESSED row formats (767 bytes for REDUNDANT and COMPACT). Descending key parts became real index order in MySQL 8.0; earlier versions parsed DESC and ignored it. Functional key parts, written with their own parentheses as ((expr)), arrived in MySQL 8.0.13.

Building it without a lock

Adding or dropping a secondary index on InnoDB happens in place, does not rebuild the table, and permits concurrent reads and writes. State ALGORITHM = INPLACE, LOCK = NONE explicitly so the statement errors out instead of silently falling back to a table copy.

Example

-- 1. Declared with the table. KEY is a synonym for INDEX, and UNIQUE KEY
--    for UNIQUE INDEX; all four spellings build the same objects.
CREATE TABLE customers (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email      VARCHAR(255)    NOT NULL,
  country    CHAR(2)         NOT NULL,
  name       VARCHAR(255)    NOT NULL,
  created_at DATETIME        NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_email (email),                  -- constraint and index in one
  KEY idx_country_created (country, created_at) -- composite secondary index
) ENGINE=InnoDB;

-- 2. Added afterwards. These two statements are equivalent: CREATE INDEX is
--    the shorthand MySQL rewrites into the ALTER TABLE longhand.
CREATE INDEX idx_name ON customers (name);
ALTER TABLE customers ADD INDEX idx_name (name);

-- 3. On a live table, name the algorithm and lock level. If InnoDB cannot
--    honour them the statement fails instead of copying the table under you.
ALTER TABLE customers
  ADD INDEX idx_created (created_at),
  ALGORITHM = INPLACE, LOCK = NONE;

-- 4. Dropping is metadata only, and has the same two spellings.
DROP INDEX idx_name ON customers;
ALTER TABLE customers DROP INDEX idx_name;

When to use it

Adding an index to a busy production table without blocking writes.
Indexing a long VARCHAR or TEXT column that exceeds InnoDB's key prefix limit.
Enforcing one-row-per-email while giving the optimizer a fast lookup.
Serving an ORDER BY that mixes ASC and DESC columns without a filesort.

More examples

Prefix indexes for long string columns

You cannot index a 2048-character URL or a MEDIUMTEXT body in full. A prefix index stores only the leading characters, which is usually selective enough to narrow the scan; MySQL then reads the row and compares the full value, so results stay exact. Use it for URLs, paths, long identifiers, and any TEXT or BLOB column, where InnoDB requires a prefix length.

CREATE TABLE pages (
  id   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  url  VARCHAR(2048)   NOT NULL,
  body MEDIUMTEXT      NOT NULL
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

-- Index the first 191 characters of url. The prefix narrows the candidates;
-- the server still compares the full column value from the row.
CREATE INDEX idx_url ON pages (url(191));

-- BLOB and TEXT columns REQUIRE a prefix length under InnoDB. Without one:
-- ERROR 1170 (42000): BLOB/TEXT column 'body' used in key specification
-- without a key length
CREATE INDEX idx_body ON pages (body(64));

-- Choose the prefix length from the data, not by guessing. Compare the
-- selectivity of the prefix against the full column.
SELECT COUNT(DISTINCT LEFT(url, 64))  / COUNT(*) AS sel_64,
       COUNT(DISTINCT LEFT(url, 191)) / COUNT(*) AS sel_191,
       COUNT(DISTINCT url)            / COUNT(*) AS sel_full
FROM pages;

Descending and functional key parts

Descending key parts matter when an ORDER BY mixes directions — a single-column DESC sort can already be served by a backward scan of an ascending index, but ASC on one column and DESC on another needs the index to store them that way. Functional key parts (MySQL 8.0.13 and later) index an expression instead of a column, which is the fix for a query whose WHERE wraps the column in a function.

CREATE TABLE posts (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  author_id    BIGINT UNSIGNED NOT NULL,
  published_at DATETIME        NOT NULL,
  title        VARCHAR(255)    NOT NULL
) ENGINE=InnoDB;

-- Mixed sort directions need matching key-part directions (MySQL 8.0+).
-- Before 8.0, DESC here was accepted and silently ignored.
CREATE INDEX idx_feed ON posts (author_id, published_at DESC, id ASC);

SELECT id, title FROM posts
WHERE author_id = 7
ORDER BY published_at DESC, id ASC
LIMIT 20;                     -- no "Using filesort" in EXPLAIN Extra

-- Functional key part. The expression carries its own parentheses, so the
-- definition shows two sets. This is built as a hidden virtual column.
CREATE INDEX idx_title_lower ON posts ((LOWER(title)));

-- Matched only when the query repeats the identical expression.
SELECT id FROM posts WHERE LOWER(title) = 'release notes';

Adding a unique index to existing data

A UNIQUE index enforces a constraint and gives the optimizer an eq_ref lookup, but adding one to a table that already contains duplicates fails outright. Find and resolve the duplicates first. The second half shows the NULL trap: UNIQUE allows unlimited NULLs, so a nullable column gets no practical protection.

-- Fails if duplicates already exist:
-- ERROR 1062 (23000): Duplicate entry '[email protected]' for key 'uq_email'
ALTER TABLE customers ADD UNIQUE KEY uq_email (email);

-- Find them first.
SELECT email, COUNT(*) AS n, GROUP_CONCAT(id ORDER BY id) AS ids
FROM customers
GROUP BY email
HAVING n > 1;

-- UNIQUE permits any number of NULLs, so this does NOT stop two rows both
-- having a NULL external_ref.
ALTER TABLE customers
  ADD COLUMN external_ref VARCHAR(64) NULL,
  ADD UNIQUE KEY uq_ref (external_ref);

INSERT INTO customers (email, country, name, created_at, external_ref)
VALUES ('[email protected]', 'SE', 'A', NOW(), NULL),
       ('[email protected]', 'SE', 'B', NOW(), NULL);   -- both succeed

-- If one row per value is the rule, the column must be NOT NULL.
ALTER TABLE customers MODIFY external_ref VARCHAR(64) NOT NULL;

Discussion

Be the first to comment on this lesson.

Key parts

Building it without a lock

Example

When to use it

More examples

Prefix indexes for long string columns

Descending and functional key parts

Adding a unique index to existing data

Related

Discussion