Create, show and drop indexes in MySQL

This covers MySQL 8.0 and later with the InnoDB storage engine; where MariaDB differs, it is noted. An index makes reads faster and every write a little slower, so add one for a query that matters and check it is used.

CREATE INDEX idx_orders_customer_created
  ON orders (customer_id, created_at);

-- the same, written as ALTER TABLE
ALTER TABLE orders ADD INDEX idx_orders_customer_created (customer_id, created_at);

Column order matters. MySQL can use a multicolumn index for a search on its leftmost columns. This index helps WHERE customer_id = 42 and WHERE customer_id = 42 AND created_at > …, but not a search on created_at alone.

Adding a secondary index to an InnoDB table normally lets reads and writes continue while it builds, but it still takes time and disk on a large table. It also needs a brief metadata lock at the start and end, so it waits for any open transaction on the table, and queries that arrive meanwhile queue behind it. Check for long-running transactions first, and try it on a copy.

DROP INDEX idx_orders_customer_created ON orders;

To test whether an index is needed without losing it, MySQL 8.0 can hide it from the optimizer first. Queries stop using it, but it is still maintained, so you can bring it back instantly.

-- MySQL 8.0 and later (MariaDB 10.6 and later uses IGNORED and NOT IGNORED instead)
ALTER TABLE orders ALTER INDEX idx_orders_customer_created INVISIBLE;
-- watch for slower queries, then either drop it or:
ALTER TABLE orders ALTER INDEX idx_orders_customer_created VISIBLE;

To find candidates, the sys schema lists indexes with no recorded use since the server started (needs Performance Schema):

SELECT * FROM sys.schema_unused_indexes;

Judge that list against how long the server has been up. An index used only by a monthly report looks unused for most of the month. Never drop a primary key or an index that backs a unique or foreign key constraint.