MySQL Index: Create, Add & Drop Tutorial

What is a MySQL Index?

An index in MySQL is a data structure that stores column values in an ordered way so the engine can look rows up quickly. Indexes are created on the column or columns most often used to filter data. Think of an index like an alphabetically sorted list: it is far faster to find a name in a sorted list than in an unsorted pile.

Indexes carry a trade-off — every INSERT or UPDATE has to maintain the index, so adding too many indexes on a write-heavy table can hurt overall performance. As a rule of thumb, index columns that appear in WHERE, JOIN, and ORDER BY clauses on tables that are read more often than they are written.

Why Use an Index?

Nobody likes slow systems. High performance is a top concern for almost every database-backed application. Businesses spend heavily on hardware to keep queries fast, but there is a ceiling to what hardware alone can deliver. Optimising indexes is a cheaper and more effective lever.

Slow response times usually come from rows being stored in physical order on disk. Without an index, MySQL must scan every row to find the ones that match a predicate — a “full table scan”. Indexes let MySQL jump straight to the matching rows, which transforms the query plan from O(n) to roughly O(log n) for B-tree lookups.

Syntax: Create Index

An index can be defined in two places:

1. At the time of table creation.
2. After the table already exists.

Example: Create an index inline with CREATE TABLE

For the myflixdb database, we expect a lot of searches on the full-name column. The script below creates a new members_indexed table with an index on the full_names column.

CREATE TABLE `members_indexed` (
    `membership_number` INT(11) NOT NULL AUTO_INCREMENT,
    `full_names`        VARCHAR(150) DEFAULT NULL,
    `gender`            VARCHAR(6)   DEFAULT NULL,
    `date_of_birth`     DATE         DEFAULT NULL,
    `physical_address`  VARCHAR(255) DEFAULT NULL,
    `postal_address`    VARCHAR(255) DEFAULT NULL,
    `contact_number`    VARCHAR(75)  DEFAULT NULL,
    `email`             VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (`membership_number`),
    INDEX (`full_names`)
) ENGINE = InnoDB;

Execute the script in MySQL Workbench against the myflixdb database.

Refresh myflixdb to see the new members_indexed table. The full_names column now appears under the Indexes node.

As membership grows, search queries on members_indexed that use WHERE and ORDER BY against full_names are much faster than the same queries on the original members table without the index.

Add an index after the table already exists

You will often discover that an existing table needs an index — search queries are slow and an EXPLAIN plan shows a full table scan on a column that appears in WHERE. The CREATE INDEX statement adds an index without recreating the table.

CREATE INDEX `id_index` ON `table_name` (`column_name`);

Concrete example — speed up searches on the title column of the movies table:

CREATE INDEX `title_index` ON `movies` (`title`);

Every query that filters on movies.title is now backed by the new index. Queries that filter on other columns still scan the table unless they have their own index.

Note: You can create a composite index across multiple columns when your queries always filter or sort on the same combination. Order matters — the leading column drives whether the index can be used.

List indexes on a table

Use SHOW INDEXES to see every index defined on a table.

SHOW INDEXES FROM `table_name`;

Example — list indexes on the movies table:

SHOW INDEXES FROM `movies`;

Run the statement in MySQL Workbench against myflixdb to see the existing indexes and the columns they cover.

Note: Primary and foreign keys are indexed automatically by MySQL. Each index has a unique name and lists the column(s) it covers.

Syntax: Drop Index

Use DROP INDEX to remove an existing index from a table. This is useful when a write-heavy table is being slowed by an index that is no longer earning its keep on the read side.

DROP INDEX `index_id` ON `table_name`;

Concrete example — drop the full_names index from members_indexed:

DROP INDEX `full_names` ON `members_indexed`;

Types of MySQL Indexes

MySQL supports several index types, each suited to a different workload.

Type	Purpose
PRIMARY KEY	Unique row identifier; clustered with the table data in InnoDB.
UNIQUE	Enforces uniqueness while serving as an index.
INDEX (B-tree)	Default secondary index used for range queries and equality lookups.
FULLTEXT	Optimised for natural-language text search with MATCH … AGAINST.
SPATIAL	R-tree index for GIS data types such as POINT and POLYGON.
HASH	Constant-time equality lookups; used by the MEMORY storage engine.
Composite (multi-column)	Combines several columns into one index; honours the leftmost-prefix rule.

Best Practices for MySQL Indexes

The habits below keep indexes useful and stop them from becoming dead weight.

• Index for the query pattern, not the column name: add indexes that match real WHERE, JOIN, and ORDER BY clauses, not “every column that sounds important”.
• Watch composite-index order: the leading column must appear in the query for the index to be used.
• Avoid duplicate indexes: a leading prefix of a composite index already covers single-column lookups on that prefix.
• Inspect with EXPLAIN: confirm the planner is actually picking the new index.
• Drop unused indexes: use sys.schema_unused_indexes in MySQL 5.7+ to find indexes that nothing reads.
• Match data types: if a WHERE clause compares a VARCHAR column to a number, the index cannot be used because of an implicit cast.

FAQs