How to Create an Index in MySQL

Having the right indexes on tables are critical to making your queries performant, especially when your data grows. Not having the needed indexes will typically result in high CPU usage in your database server, slow response times, and ultimately unhappy users.

To create indexes, use the CREATE INDEX command:

CREATE INDEX index_name ON table_name (column_name);

You can an index on multiple columns. When used for columns that are frequently used together as filters, a multiple-column index performs better than multiple single-column indexes:

CREATE INDEX user_id_and_org_id_idx ON users (user_id, org_id);

A unique index prevents duplicate data:

CREATE UNIQUE INDEX users_email_uq ON users (email);

Sometimes you want to index only the first N characters of a string:

CREATE INDEX company_part_name_idx ON companies (name(20));

Starting with MySQL version 8.x you can also specify the storage order of a column in an index. This can be useful if you also need to display the column in a certain order. If unspecified, this defaults to ascending order:

CREATE INDEX reverse_name_idx ON companies (name DESC);

MySQL 8.0.13 and higher also supports functional key parts. These index expression values rather than column or column prefix values. They enable indexing of values not stored directly in the table. Note the use of double parentheses.

-- The following will enable fast filtering by total line item amount
CREATE INDEX line_item_idx ON order_line_items ((product_price * quantity));