Creating Indexes

Creating Primary, Unique, and Plain Indexes

A table has one PRIMARY KEY (never NULL), any number of UNIQUE indexes (no duplicates, many NULLs allowed) and plain INDEXes (synonym KEY). Declare them in CREATE TABLE or add them with ALTER TABLE ... ADD INDEX or CREATE INDEX; ALGORITHM = INPLACE, LOCK = NONE demands an online build (ALTER TABLE and Online DDL). Foreign keys get an index automatically.

A duplicate rejected, and a lookup through the unique indexSQL

INSERT INTO customers (email, name, country) VALUES ('chen@example.com', 'Chen Two', 'MY');
EXPLAIN FORMAT=TRADITIONAL SELECT id FROM customers WHERE email = 'chen@example.com'\G

Output

ERROR 1062 (23000): Duplicate entry 'chen@example.com' for key 'customers.email'
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customers
   partitions: NULL
         type: const
possible_keys: email
          key: email
      key_len: 1022
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.000 sec)

In the traditional plan, type: const means one row via a unique index, key the index used, rows the estimate, and Using index that the index alone answered (id is in every entry). Without an index: type: ALL, key: NULL, rows near the table size. MySQL 9.7 524 prints the tree format by default (explain_format = TREE; Reading EXPLAIN Output).

MENU

Creating Indexes