MENU


Indexes

Indexes speed up searching when retrieving data for queries. Most indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees; indexes on spatial data types use R-trees, and MEMORY tables also support hash indexes. Indexes allow rows to be retrieved quickly and speed up joins, sorting, grouping, MIN(), MAX(), and row elimination.

The VECTOR column type (see Data Types) is a related but separate case: a VECTOR column cannot be used as any kind of key, and there is no B-tree or R-tree equivalent for it on a self-hosted server. An approximate nearest-neighbor VECTOR INDEX, built on the HNSW algorithm, does exist for fast large-scale similarity search, but it is a HeatWave-only capability, not something CREATE INDEX can build on MySQL Community or Commercial Edition.

Without an index, MySQL reads through the entire table to find requested rows – for a table with 1,000,000 rows, using an index to find a row can reduce search time by a factor of at least 50,000. However, when a query needs to access most of the rows in a table, reading sequentially is faster than working through an index.

PRIMARY KEY, UNIQUE, INDEX

A table can have only one PRIMARY KEY, which must not be NULL. A table can have multiple UNIQUE fields, which can be NULL; like PRIMARY KEY, UNIQUE fields must not contain duplicates. Unlike PRIMARY KEY and UNIQUE columns, an INDEX column allows duplicate values.


CREATE TABLE tbl(
   a INT,
   b INT,
   INDEX USING BTREE (a,b)
);

INSERT INTO tbl VALUES (1,2),(1,2); -- an INDEX column allows duplicates
Most indexes are created as part of a table definition, but an index can be added later with ALTER TABLE, or with CREATE INDEX, which MySQL maps internally to an ALTER TABLE statement.

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
  [index_type]
  ON tbl_name (index_col_name,...)
  [index_option]
  [algorithm_option | lock_option] ...

index_col_name: col_name [(length)] [ASC | DESC]
index_type: USING {BTREE | HASH}
index_option: KEY_BLOCK_SIZE [=] value | index_type | WITH PARSER parser_name
  | COMMENT 'string' | {VISIBLE | INVISIBLE}
  | ENGINE_ATTRIBUTE [=] 'string' | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'

DROP INDEX index_name ON tbl_name [algorithm_option | lock_option] ...

algorithm_option: ALGORITHM [=] {DEFAULT|INPLACE|COPY}
lock_option: LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

CREATE INDEX / DROP INDEX syntax. See Table Definitions for ALGORITHM and LOCK details under ALTER TABLE.


Prefixes can be specified for CHAR, VARCHAR, BINARY, and VARBINARY columns; a prefix length is mandatory for BLOB and TEXT. Prefix values cannot be given for spatial columns. FULLTEXT indexes are supported only for InnoDB and MyISAM tables and can include only CHAR, VARCHAR, and TEXT columns – indexing always happens over the entire column. KEY_BLOCK_SIZE is a hint for the page size in bytes. InnoDB and MyISAM support only the BTREE index type; MEMORY/HEAP/NDB support both BTREE and HASH.


CREATE TABLE tbl(
   name VARCHAR(64)
) ENGINE=MEMORY;

CREATE INDEX nameInd USING HASH ON tbl(name(10));
DROP INDEX nameInd ON tbl;

FOREIGN KEY

Foreign keys cross-reference related data across tables. A foreign key value can only be created in the child table if there is a matching candidate key value in the parent table. The FOREIGN KEY clause is specified in the child table. The parent and child tables must use the same storage engine, and neither may be a TEMPORARY table. Corresponding columns must have similar data types – the size and sign of integer types must match, though the length of string types need not; character set and collation must match. If a CONSTRAINT symbol clause is given, the symbol value must be unique within the database.

ON UPDATE / ON DELETE reference_option:
CASCADE – delete or update the matching rows in the child table automatically.
SET NULL – set the foreign key columns in the child table to NULL.
RESTRICT (default) – reject the deletion or update on the parent table.
NO ACTION – same as RESTRICT in MySQL.
SET DEFAULT – recognized by the MySQL parser, but rejected by InnoDB.


DROP TABLE IF EXISTS parent1,parent2,child;
CREATE TABLE parent1(
   a INT,
   b INT,
   PRIMARY KEY (a,b)
);
CREATE TABLE parent2(
   c VARCHAR(10) PRIMARY KEY
);
CREATE TABLE child(
   x INT,
   y INT,
   z VARCHAR(10),
   FOREIGN KEY (x,y) REFERENCES parent1(a,b)
      ON DELETE CASCADE
      ON UPDATE RESTRICT,
   CONSTRAINT zc
      FOREIGN KEY (z) REFERENCES parent2(c)
      ON DELETE CASCADE
);
INSERT INTO parent1 VALUES (1,2);
INSERT INTO parent2 VALUES ('hello');
INSERT INTO child VALUES (1,2,'hello');
-- INSERT INTO child VALUES (1,2,'world'); -- this fails! no matching parent2.c

ALTER TABLE child DROP FOREIGN KEY zc;
INSERT INTO child VALUES (1,2,'world'); -- this works! zc constraint is gone
Foreign keys can be configured to cascade updates or deletes to related tables automatically – for example, if a foreign key in an orders table refers to a primary key in a customers table, deleting a customer can be configured to automatically delete all of that customer's orders (see the worked example). To disable foreign key checking, use SET GLOBAL FOREIGN_KEY_CHECKS=0;.


CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50)
);
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);
INSERT INTO customers VALUES (1, 'Alice');
INSERT INTO customers VALUES (2, 'Bob');
INSERT INTO orders VALUES (1, '2022-01-01', 1);
INSERT INTO orders VALUES (2, '2022-02-01', 1);
INSERT INTO orders VALUES (3, '2022-03-01', 2);
UPDATE customers SET customer_id = 3 WHERE customer_id = 1;
DELETE FROM customers WHERE customer_id = 2;
SELECT * FROM customers JOIN orders;

Query OK, 1 row affected Query OK, 1 row affected
customer_idcustomer_nameorder_idorder_datecustomer_id
3Alice12022-01-013
3Alice22022-02-013

FULLTEXT

Fulltext search enables searching text-based columns for specific keywords or phrases, useful for search functionality and more complex text analysis such as natural language processing. It is powered by fulltext indexes, which store additional information about the words or phrases in a text column, speeding up keyword searches over large datasets.

A query using MATCH (col1,col2) AGAINST ('word' IN NATURAL LANGUAGE MODE) returns rows sorted by relevance, most relevant first. The built-in MySQL full-text parser uses whitespace between words as a delimiter, which is a limitation for ideographic languages without word delimiters. MySQL provides an ngram full-text parser (WITH PARSER ngram) that supports Chinese, Japanese, and Korean (CJK). The MeCab plugin performs morphological analysis – breaking words into root words and suffixes – useful for languages with complex grammar such as Japanese or Korean; it is installed with INSTALL PLUGIN mecab SONAME 'libpluginmecab.so'; and used via WITH PARSER mecab.


CREATE TABLE IF NOT EXISTS articles (
   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
   title VARCHAR(200),
   body TEXT,
   FULLTEXT (title,body)
) ENGINE=InnoDB;

INSERT INTO articles (title,body) VALUES
   ('MySQL Tutorial','DBMS stands for DataBase ...'),
   ('How To Use MySQL Well','After you went through a ...'),
   ('Optimizing MySQL','In this tutorial, we show ...'),
   ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
   ('MySQL vs. YourSQL','In the following database comparison ...'),
   ('MySQL Security','When configured properly, MySQL ...');

SELECT * FROM articles
   WHERE MATCH (title,body)
   AGAINST ('database' IN NATURAL LANGUAGE MODE);

idtitlebody
1MySQL TutorialDBMS stands for DataBase ...
5MySQL vs. YourSQLIn the following database comparison ...

-- ngram parser, for CJK (Chinese/Japanese/Korean) text without word delimiters
CREATE TABLE articles (
      id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
      title VARCHAR(200),
      body TEXT,
      FULLTEXT (title,body) WITH PARSER ngram
    ) ENGINE=InnoDB CHARACTER SET utf8mb4;

INSERT INTO articles (title,body) VALUES
    ('',''),
    ('','');

-- MeCab performs morphological analysis for languages such as Japanese
INSTALL PLUGIN mecab SONAME 'libpluginmecab.so';
CREATE FULLTEXT INDEX idx_content_mecab ON japanese_articles(content)
      WITH PARSER mecab;
SELECT COUNT(*) FROM japanese_articles WHERE MATCH(content)
      AGAINST('*' IN BOOLEAN MODE);

Multi-value JSON

Multi-value indexes for JSON speed up searching for specific values inside JSON documents stored in a column. For example, given a JSON document with an "interests": ["hiking", "reading", "traveling"] array stored in a column named data, a multi-value index on the interests field can be created with CAST(data->'$.interests' AS CHAR(50) ARRAY). Multi-value indexes can be defined inline in CREATE TABLE, added later with ALTER TABLE ADD INDEX, added with CREATE INDEX, or included as part of a composite index alongside ordinary columns – see the worked example.


-- illustrative: assumes a Persons table with a JSON `data` column holding
-- documents shaped like {"name": "John Doe", "age": 30, "interests": [...]}
CREATE INDEX idx ON Persons((CAST(data->'$.interests' AS CHAR(50) ARRAY)));

CREATE TABLE customers (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    modified DATETIME DEFAULT CURRENT_TIMESTAMP
                                   ON UPDATE CURRENT_TIMESTAMP,
    custinfo JSON,
    INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) )
);

-- equivalently, add it later:
ALTER TABLE customers
    ADD INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );

-- or with CREATE INDEX:
CREATE INDEX zips ON customers
    ((CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );

-- a multi-valued index as part of a composite index
ALTER TABLE customers
   ADD INDEX comp(id, modified, (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );

INVISIBLE

Invisible indexes are indexes that the MySQL query optimizer does not use. They make it possible to test the effect of removing an index on query performance without making a destructive change that must be undone if the index turns out to be required – dropping and re-adding an index can be expensive for a large table, whereas making it invisible and visible again are fast, in-place operations. An index can be marked INVISIBLE in its CREATE TABLE, CREATE INDEX, or ALTER TABLE ADD INDEX definition, and toggled afterward with ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE; / VISIBLE;.


CREATE TABLE t1 (
    i INT,
    j INT,
    k INT,
    INDEX i_idx (i) INVISIBLE
) ENGINE = InnoDB;
CREATE INDEX j_idx ON t1 (j) INVISIBLE;
ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE;

-- toggle a visible/invisible index without an expensive drop+recreate
ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE;
ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;

Descending Indexes

MySQL supports descending indexes, which can be more efficient. They also let the optimizer use multiple-column indexes when the most efficient scan order mixes ascending order for some columns with descending order for others – see the worked example, where four two-column indexes cover every combination of ASC/DESC on c1 and c2, each matching a different ORDER BY.


CREATE TABLE t (
  c1 INT, c2 INT,
  INDEX idx1 (c1 ASC, c2 ASC),
  INDEX idx2 (c1 ASC, c2 DESC),
  INDEX idx3 (c1 DESC, c2 ASC),
  INDEX idx4 (c1 DESC, c2 DESC)
);
-- ...ORDER BY c1 ASC, c2 ASC;   -- optimizer can use idx1
-- ...ORDER BY c1 DESC, c2 DESC; -- optimizer can use idx4
-- ...ORDER BY c1 ASC, c2 DESC;  -- optimizer can use idx2
-- ...ORDER BY c1 DESC, c2 ASC;  -- optimizer can use idx3

GIPK

A Generated Invisible Primary Key (GIPK) is an invisible primary key, named my_row_id, automatically generated for a table that has no explicit primary key. The feature must be turned on first with SET sql_generate_invisible_primary_key=ON;. The generated column can be revealed with ALTER TABLE tableX ALTER COLUMN my_row_id SET VISIBLE;.


SET sql_generate_invisible_primary_key=ON;
SELECT @@sql_generate_invisible_primary_key;

CREATE TABLE tableX (c1 VARCHAR(50), c2 INT);
SHOW CREATE TABLE tableX;

ALTER TABLE tableX ALTER COLUMN my_row_id SET VISIBLE;

@@sql_generate_invisible_primary_key
1
TableCreate Table
tableXCREATE TABLE `tableX` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `c1` varchar(50) DEFAULT NULL,
  `c2` int DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CACHE INDEX

In MySQL, a cache index (also called a key cache, related to the buffer pool) is a memory area used to store frequently accessed data and index pages, reducing physical reads and writes to disk. When a query runs, the server checks whether the required data is already in the cache; if so it is returned directly, otherwise it is read from disk and added to the cache for future access. The buffer pool is managed dynamically by the server and can also be sized manually via system variables such as innodb_buffer_pool_size (InnoDB) or key_buffer_size (MyISAM).

The CACHE INDEX and LOAD INDEX INTO CACHE statements work only with the MyISAM storage engine.

CACHE INDEX tbl_index_list [, tbl_index_list] ... [PARTITION (partition_list | ALL)] IN key_cache_name
tbl_index_list: tbl_name [[INDEX|KEY] (index_name[, index_name] ...)]
partition_list: partition_name[, partition_name][, ...]

Assigns table indexes to a specific key cache. A key cache must already exist, created by setting its size, e.g. SET GLOBAL keycache1.key_buffer_size=128*1024;. Index assignment affects the server globally.


LOAD INDEX INTO CACHE tbl_index_list [, tbl_index_list] ...
tbl_index_list: tbl_name [PARTITION (partition_list | ALL)] [[INDEX|KEY] (index_name[, index_name] ...)] [IGNORE LEAVES]

Preloads a table index into the key cache it was assigned to via CACHE INDEX, or into the default key cache otherwise. IGNORE LEAVES preloads only blocks for the nonleaf nodes of the index, and fails unless all indexes in the table share the same block size (checked with myisamchk -dv, Blocksize column).



CREATE TABLE pt (c1 INT, c2 VARCHAR(50), INDEX i(c1))
    PARTITION BY HASH(c1)
    PARTITIONS 4;

SET GLOBAL kc_fast.key_buffer_size = 128 * 1024;
SET GLOBAL kc_slow.key_buffer_size = 128 * 1024;

CACHE INDEX pt PARTITION (p0) IN kc_fast;
CACHE INDEX pt PARTITION (p1, p3) IN kc_slow;

-- preload a table's indexes into whichever key cache they were assigned to
LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES;