Comprehensive Guide to MySQL Indexes, Types, and Query Optimization

MySQL defines an index as a data structure that helps the engine retrieve rows efficiently. In simple terms, an index is a sorted structure that enables fast look‑ups, similar to a book's table of contents.

The most common index structures in MySQL are B‑Tree (MyISAM ordinary index), B+‑Tree (InnoDB ordinary index) and Hash (Memory engine). B‑Tree can degenerate into a linked list when rows are inserted in increasing order, reducing lookup efficiency.

Advantages of indexes include reduced I/O cost for data retrieval and lower CPU consumption for sorting because the data is already ordered by the index.

Disadvantages are additional storage overhead (indexes are separate tables storing key columns) and slower write performance, as every INSERT/UPDATE/DELETE must also modify the index files.

When to create indexes :

Primary key (automatically creates a unique index)

Columns frequently used in WHERE clauses

Foreign key columns used for joins

Multi‑column queries (create composite indexes)

Columns used for ORDER BY to speed up sorting

Columns used for GROUP BY or aggregation

When not to create indexes :

Tables with very few rows

Highly volatile tables with frequent writes

Columns that are never used in query conditions

Index Types

Primary Key Index

Created automatically when a column is defined as PRIMARY KEY.

ALTER TABLE table_name ADD PRIMARY KEY (column);

Unique Index

Ensures column values are unique; also created automatically for UNIQUE constraints.

ALTER TABLE table_name ADD UNIQUE idx_name(column);
DROP INDEX idx_name ON table_name;

Single‑Column Index

Indexes a single column; a table can have many of these.

ALTER TABLE table_name ADD INDEX idx_name(column);
DROP INDEX idx_name ON table_name;

Composite Index

Indexes multiple columns together.

ALTER TABLE table_name ADD INDEX idx_name(col1, col2);
DROP INDEX idx_name ON table_name;

Performance Analysis

Common MySQL Bottlenecks

CPU bottleneck when comparing, joining, sorting, or grouping large datasets.

Insufficient memory causing excessive physical I/O.

EXPLAIN

Using the EXPLAIN keyword shows how MySQL will execute a query, including read order, used indexes, join types, and estimated rows.

EXPLAIN SELECT * FROM wk1, wk2, wk3 WHERE wk1.id = wk2.id AND wk2.id = wk3.id;

Important EXPLAIN fields: SIMPLE: simple SELECT without subqueries or UNION. PRIMARY: outermost query when subqueries exist. DERIVED: subqueries in FROM clause (MySQL 5.7+). SUBQUERY: subqueries in SELECT or WHERE.

Key fields such as type indicate access method (system, const, eq_ref, ref, range, index, all) with the order from best to worst being system > const > eq_ref > ref > range > index > all. The possible_keys column lists indexes that could be used, while key shows the actual index chosen. key_len reflects the length of the used index portion, and rows estimates how many rows MySQL expects to examine.

Common extra messages: Using filesort: MySQL cannot use an index for ORDER BY. Using temporary: a temporary table is created for sorting or grouping. Using index: the query is satisfied entirely from the index. Using where: a WHERE filter is applied.

Query Optimization

Index Invalidations

Follow the left‑most prefix rule for composite indexes.

Avoid functions or calculations on indexed columns.

Range conditions cannot use columns to the right of the range.

‘!=’ and ‘IS NOT NULL’ prevent index usage.

Leading wildcards in LIKE disable index usage.

Missing quotes around string literals break index usage.

OR conditions often cause full table scans.

-- Example of index invalidation
EXPLAIN SELECT * FROM students WHERE LEFT(sname,2) = '小明';

Composite Index Practice

Creating a composite index on (category_id, comments, views) improves the following query:

EXPLAIN SELECT id, author_id FROM article WHERE category_id=1 AND comments>1 ORDER BY views DESC LIMIT 1;

After adding the index, the query uses the composite index and avoids filesort.

Single‑Table Query Optimization

CREATE TABLE IF NOT EXISTS article (
  id INT(10) PRIMARY KEY AUTO_INCREMENT,
  author_id INT NOT NULL,
  category_id INT NOT NULL,
  views INT NOT NULL,
  comments INT NOT NULL,
  title VARBINARY(255) NOT NULL,
  content TEXT NOT NULL
);

ALTER TABLE article ADD INDEX idx_ccv(category_id, comments, views);

Join Optimization

For inner joins, MySQL drives the smaller result set; ensure the larger table’s join columns are indexed. For left outer joins, index the right‑hand table’s join columns.

Order‑by Optimization

Avoid USING FILESORT by ensuring ORDER BY columns follow an index's left‑most prefix.

Do not combine WHERE range conditions that break the index order.

Group‑by Optimization

ALTER TABLE students ADD INDEX idx_sas(sname, age, score);
EXPLAIN SELECT COUNT(*), sname FROM students WHERE sname='小明' AND age>22 GROUP BY score;

Slow Query Log

The slow query log records statements whose execution time exceeds long_query_time. Enable it with:

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;  -- seconds

Check the log file (e.g., /data/...-slow.log) to identify problematic queries.

-- Example stored procedure to generate test data
CREATE PROCEDURE insert_person(IN max_num INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  SET autocommit = 0;
  REPEAT
    SET i = i + 1;
    INSERT INTO person (PID, PNAME, PSEX, PAGE, SAL)
    VALUES (i, CONCAT('test', FLOOR(RAND()*10000000)),
            IF(RAND()>0.5,'男','女'),
            FLOOR(RAND()*100)+10,
            FLOOR(RAND()*19000)+1000);
  UNTIL i = max_num END REPEAT;
  COMMIT;
END;

After generating data, you can view the slow‑query log to see which statements need further tuning.