Indexes in MySQL – Notes

Indexes are used to find rows in a database fast. Without indexes the database has to start from first row and find the required rows until the last row, sequentially (a.k.a. full table scan). Full table scans are slow on tables with large number of rows.

MySQL uses the indexes to point to the rows as specified in the query criteria and retrieve them quickly. In MySQL you can create different types of indexes based upon your query, search and performance requirement.

Here is some info about types of indexes, associated commands and index usage by queries.

Index Types

• Column index - An index on a single column.
• Composite index - An index on multiple columns. A.k.a. multi-column index, concatenated index.
• Unique index - An index on column(s) that have no duplicate values.
• Primary key index - A special index that stores row data for InnoDB tables. If a primary key is not defined, the first Unique index is used for this. Otherwise, a hidden index is created on a synthetic column of row id values. A.k.a. Clustered index.
• Secondary index - A non-primary key index.
• Covering index - An index that includes all the columns retrieved by a query. Instead of using the index values as pointers to find the full table rows, the query returns values from the index structure, thus saving disk I/O.
• Partial index - An index that represents only part of a column value, typically the first N characters of a long VARCHAR value.
• Spatial index - An index created on spatial data type (e.g., POINT and GEOMETRY) columns. Spatial data is also known as geographic data or geospatial data. You can also create non-spatial indexes on spatial data types.
• Fulltext index - An index that holds the search index in the MySQL full-text search mechanism. This is for text-based data types (CHAR, VARCHAR, or TEXT).
• Hash index - A type of index intended for queries that use equality operators, rather than range operators such as greater-than or BETWEEN. It is available for MEMORY tables.
• Adaptive hash index - An optimization for InnoDB tables that can speed up lookups using = and IN operators, by constructing a hash index in memory.
• Descending index - An index where index storage is optimized to process ORDER BY column DESC clauses.
• Invisible index - An index that is not used by the optimizer. Invisible index makes it possible to test the effect of removing an index on query performance without dropping it.
• Virtual index - An index on virtual or stored generated columns or a combination of regular and generated columns.
• Multi-Valued index - An index defined on a JSON column that stores an array of values.

Creating Indexes

You can create an index, modify, delete or query the database to see what indexes are there.

Here are some commands and their usage:

• Create using CREATE TABLE INDEX, ALTER TABLE ADD INDEX, CREATE INDEX
• Modify using ALTER TABLE ALTER INDEX, ALTER TABLE RENAME INDEX
• Delete using ALTER TABLE DROP INDEX, DROP INDEX
• List indexes using SHOW INDEXES

Example Usage:

-- Create a table and an index on two columns c1 and c2
CREATE TABLE t1 (
  c1 VARCHAR(10),
  c2 VARCHAR(15),
  c3 VARCHAR(30),
  c4 DATETIME,
  INDEX ix1 (c1, c2)
);

-- Add a unique index
ALTER TABLE t1 ADD UNIQUE INDEX uix1 (c4);

-- List the indexes
SHOW INDEXES FROM t1\G

-- Create a partial index
CREATE INDEX part_of_c3 ON t1 (c3(10));

-- Add a JSON data column to the table
-- Create an index on its name field (NOTE: your collation may be different value)
ALTER TABLE t1 ADD COLUMN jdata JSON;
DESCRIBE t1;
CREATE INDEX jdata_name ON t1 ((CAST(jdata->>"$.name" AS CHAR(20)) COLLATE cp850_general_ci));

-- Delete an index (following two statements has same result)
ALTER TABLE t1 DROP INDEX uix1;
DROP INDEX uix1 ON t1;

SHOW INDEXES FROM t1;

Verifying Index Usage

You can check whether your queries really use the indexes created in the tables. For this, use the EXPLAIN statement. EXPLAIN provides information from the optimizer about how MySQL would execute a query. With the help of EXPLAIN, you can see where you should add indexes to tables so that the statement executes faster by using indexes to find rows.

EXPLAIN works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements. Here is an example using SELECT on this table test2:

CREATE TABLE test2 (
  c1 int NOT NULL,
  c2 varchar(10),
  PRIMARY KEY (c1)
);

Sample rows in table:

mysql> SELECT * FROM test2;
+-----+---------+
| c1  | c2      |
+-----+---------+
|   1 | NULL    |
|   5 | five    |
|   4 | four    |
|   3 | three   |
|   2 | two     |
...
+-----+---------+

List the table indexes and check the usage in a SELECT query:

mysql> SHOW INDEX IN test2\G
*************************** 1. row ***************************
        Table: test2
   Non_unique: 0
     Key_name: PRIMARY
  Column_name: c1
...


mysql> EXPLAIN SELECT * FROM test2 WHERE c1 >= 3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test2
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
...

mysql> EXPLAIN ANALYZE SELECT * test2 WHERE c1 >= 3\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (test2.c1 >= 3)  (cost=1.46 rows=6) (actual time=0.016..0.0251 rows=6 loops=1)
    -> Index range scan on test2 using PRIMARY over (3 <= c1)  (cost=1.46 rows=6) (actual time=0.0143..0.0224 rows=6 loops=1)

EXPLAIN ANALYZE (introduced in MySQL 8.0.18) runs a statement and produces EXPLAIN output along with timing and additional information about how the optimizer's expectations matched the actual execution.

Useful Notes

• Index prefix - The initial or leading columns of a composite index. A query that references the first few columns of a composite index can use the index, even if the query does not reference all the columns in the index.
• Index hints - Index hints give the optimizer information about how to choose indexes during query processing (for overriding the indexes recommended by the optimizer). Index hints apply to SELECT, UPDATE and DELETE statements.
• Index extensions - InnoDB automatically extends each secondary index by appending the primary key columns to it. If a table has a column c1 as primary key and column c2 has an index, MySQL (optimizer) treats the index on c2 as c2+c1 columns.
• Full table scan - A query that requires reading the entire contents of a table, rather than just selected portions using an index.
• Optimizer - The MySQL component that determines the best indexes and join order to use for a query, based on characteristics and data distribution of the relevant tables. A.k.a. Query Optimizer.
• Index data structures - Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Some exceptions are (a) indexes on spatial data types use R-trees, (b) MEMORY tables use hash indexes by default, and (c) InnoDB uses inverted lists for FULLTEXT indexes.
• MySQL storage engines - Storage engines are MySQL components that handle the SQL operations for different table types. InnoDB is the default and most general-purpose storage engine. There are also MyISAM, MEMORY, CSV, NDB, and others. Each of these have their specific purpose. Some indexes are specific to some storage engines.

You can install MySQL Server on your computer by following the instructions from this GitHub Gist Basic steps to install MySQL 8 Server and create database data and try the above code snippets.

For further reference see MySQL 8.0 Reference Manual.