Understanding MySQL Indexes: Creation, Usage, and Optimization

About MySQL

MySQL is an open‑source relational database management system originally developed by MySQL AB and now owned by Oracle.

After Oracle’s acquisition, concerns about continued support for the community edition led the original founder to create MariaDB as a fork.

MySQL’s high performance, low cost and reliability have made it one of the most popular open‑source databases, widely used in LAMP stacks and large‑scale web applications.

Why MySQL

Key advantages of MySQL among free open‑source RDBMSs include fast execution speed, ease of use, full SQL support, good portability, rich feature set and low cost.

Official benchmarks show MySQL 8.0 delivering roughly twice the throughput of MySQL 5.7, with read‑only performance exceeding one million operations per second and read‑write performance around 250 k ops/s.

MySQL Index

Why Index

Indexes dramatically reduce the time needed to retrieve rows that match a SELECT statement, making query optimization essential.

When a query is slow, adding an appropriate index is usually the fastest way to improve performance.

How Index Works

A SELECT creates a temporary result set; an index allows MySQL to locate the needed rows without scanning the whole table.

Creating a Demo Table

CREATE DATABASE ijiangtao_local_db_mysql /*!40100 DEFAULT CHARACTER SET utf8 */;
USE ijiangtao_local_db_mysql;
DROP TABLE IF EXISTS t_user_action_log;
CREATE TABLE `t_user_action_log` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT 'primary key id',
  `name` VARCHAR(32) DEFAULT NULL COMMENT 'username',
  `ip_address` VARCHAR(50) DEFAULT NULL COMMENT 'IP address',
  `action` INT4 DEFAULT NULL COMMENT 'operation: 1‑login, 2‑logout, 3‑shopping, 4‑return, 5‑browse',
  `create_time` TIMESTAMP COMMENT 'creation time',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) VALUES
('LiSi','8.8.8.2',1,CURRENT_TIMESTAMP),
('LiSi','8.8.8.1',2,CURRENT_TIMESTAMP),
('LiSi','8.8.8.2',1,CURRENT_TIMESTAMP),
('LiSi','8.8.8.3',1,CURRENT_TIMESTAMP),
('LiSi','8.8.8.2',2,CURRENT_TIMESTAMP),
('LiSi','8.8.8.4',1,CURRENT_TIMESTAMP),
('LiSi','8.8.8.2',2,CURRENT_TIMESTAMP),
('LiSi','8.8.8.1',1,CURRENT_TIMESTAMP),
('LiSi','8.8.8.2',2,CURRENT_TIMESTAMP),
('LiSi','8.8.8.2',3,CURRENT_TIMESTAMP),
('LiSi','8.8.8.2',5,CURRENT_TIMESTAMP),
('LiSi','8.8.8.2',2,CURRENT_TIMESTAMP),
('LiSi','8.8.8.2',3,CURRENT_TIMESTAMP),
('LiSi','8.8.8.2',4,CURRENT_TIMESTAMP);

To find rows where action = 2:

SELECT id, name, ip_address FROM t_user_action_log WHERE `action` = 2;

Running EXPLAIN on the query shows type = ALL, meaning a full table scan.

Adding an index on action:

ALTER TABLE t_user_action_log ADD INDEX ( `action` );

After the index, EXPLAIN reports type = ref and the Extra column becomes empty, confirming the index is used.

Index Usage Principles

Avoid indexing columns that are frequently updated, because each write must also update the index and can degrade insert/delete performance.

Each additional index consumes disk space; use indexes judiciously.

Index columns used in WHERE, JOIN, GROUP BY or ORDER BY clauses, not columns that are only returned in the SELECT list.

High‑cardinality (many distinct values) columns benefit most from indexing; low‑cardinality columns (e.g., gender) often do not.

Prefer short index lengths; shorter keys reduce I/O and improve lookup speed.

For string columns, index only a prefix when the leading characters are sufficiently selective.

In composite indexes, the leftmost column is always usable; design the column order accordingly.

InnoDB’s clustered primary key should be an auto‑increment integer to keep secondary indexes compact.

Choose the appropriate index type: B‑tree for most queries, hash for equality lookups on MEMORY tables.

Creating, Viewing, and Dropping Indexes

Create Index

Three ways:

CREATE INDEX index_name ON table_name (column_name(length));

Example on ip_address with a 16‑byte prefix:

CREATE INDEX index_ip_addr ON t_user_action_log (ip_address(16));

Or using ALTER TABLE:

ALTER TABLE table_name ADD INDEX index_name (column_name);

View Indexes

SHOW INDEX FROM t_user_action_log;

Drop Index

ALTER TABLE t_user_action_log DROP INDEX index_ip_addr;

Covering Indexes

A covering index contains all columns required by the query, allowing MySQL to satisfy the request using only the index.

When EXPLAIN shows Extra = Using index, the query is covered.

EXPLAIN SELECT `action` FROM ijiangtao_local_db_mysql.t_user_action_log;

Clustered Indexes

InnoDB stores the primary key together with the row data (clustered index). Only one clustered index is allowed; use a short, auto‑increment integer as the primary key.

String columns, especially random strings, are not recommended as clustered keys because they cause frequent page splits.

Query Optimization Tips

Use EXPLAIN to inspect select_type, type, key, possible_keys, rows, and Extra fields.

Declare columns NOT NULL when possible to avoid null checks.

Prefer numeric types over strings for better performance and smaller storage.

Use ENUM for columns with a limited set of values.

Summary

Indexes are separate on‑disk data structures that sort one or more column values and store pointers to the original rows. Proper index design—considering write frequency, cardinality, length, and type—combined with regular use of EXPLAIN can dramatically improve MySQL query performance for developers and DBAs alike.