MySQL Index Types, Creation Methods, and Optimization Strategies

Overview

Indexes are data structures used by storage engines to locate records quickly; proper use of MySQL indexes can dramatically improve query performance. The discussion focuses on InnoDB B‑Tree indexes.

Advantages of Indexes

1. Reduce the amount of data the server must scan, speeding up retrieval. 2. Help avoid sorting and temporary tables. 3. Convert random I/O into sequential I/O.

Index Creation

3.1 Primary key index

ALTER TABLE 'table_name' ADD PRIMARY KEY 'index_name' ('column');

3.2 Unique index ALTER TABLE 'table_name' ADD UNIQUE 'index_name' ('column'); 3.3 Normal index ALTER TABLE 'table_name' ADD INDEX 'index_name' ('column'); 3.4 Full‑text index

ALTER TABLE 'table_name' ADD FULLTEXT 'index_name' ('column');

3.5 Composite index

ALTER TABLE 'table_name' ADD INDEX 'index_name' ('column1', 'column2', ...);

B+Tree Index Rules

Example table creation:

DROP TABLE IF EXISTS user_test;<br/>CREATE TABLE user_test (<br/>  id int AUTO_INCREMENT PRIMARY KEY,<br/>  user_name varchar(30) NOT NULL,<br/>  sex bit(1) NOT NULL DEFAULT b'1',<br/>  city varchar(50) NOT NULL,<br/>  age int NOT NULL<br/>) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Creating a composite index:

ALTER TABLE user_test ADD INDEX idx_user(user_name, city, age);

Effective Index Strategies

5.1 Index columns cannot be part of expressions or function arguments, otherwise the index cannot be used.

SELECT * FROM user_test WHERE user_name = CONCAT(user_name, 'fei');

5.2 Prefix indexes

When indexing long character columns, a prefix index can reduce storage and improve efficiency.

5.2.1 Selecting prefix length

SELECT COUNT(DISTINCT index_column)/COUNT(*) FROM table_name;

or

SELECT COUNT(DISTINCT LEFT(index_column,1))/COUNT(*),<br/>       COUNT(DISTINCT LEFT(index_column,2))/COUNT(*),<br/>       COUNT(DISTINCT LEFT(index_column,3))/COUNT(*)<br/>FROM table_name;

5.2.2 Creating a prefix index

ALTER TABLE table_name ADD INDEX index_name (index_column(length));

5.2.3 Limitations

MySQL cannot use a prefix index for ORDER BY, GROUP BY, or covering scans.

5.3 Choosing column order in composite indexes

Place the most selective column first; adjust based on query patterns.

5.4 Clustered vs. Non‑clustered indexes

Clustered indexes determine the physical order of rows; a table can have only one. InnoDB uses the primary key as the clustered index.

Non‑clustered indexes store only the index keys and pointers to rows.

5.5 Covering indexes

If an index contains all columns needed by a query, the query can be satisfied using only the index.

SELECT user_name, city, age FROM user_test WHERE user_name = 'feinik' AND age > 25;

5.6 Using indexes for sorting

To sort using an index, the ORDER BY column order must match the index column order and direction, and the selected columns must be covered by the index.

Examples of index‑based sorting:

SELECT user_name, city, age FROM user_test ORDER BY user_name;<br/>SELECT user_name, city, age FROM user_test ORDER BY user_name, city;<br/>SELECT user_name, city, age FROM user_test ORDER BY user_name DESC, city DESC;<br/>SELECT user_name, city, age FROM user_test WHERE user_name = 'feinik' ORDER BY city;

Examples where index sorting cannot be used:

SELECT user_name, city, age FROM user_test ORDER BY user_name, sex;<br/>SELECT user_name, city, age FROM user_test ORDER BY user_name ASC, city DESC;<br/>SELECT user_name, city, age, sex FROM user_test ORDER BY user_name;

When a WHERE clause uses a range condition on the leftmost index column, subsequent columns cannot be used for index sorting.

SELECT user_name, city, age FROM user_test WHERE user_name LIKE 'feinik%' ORDER BY city;

In multi‑table joins, index sorting works only if the ORDER BY columns are indexed in the first table.

DROP TABLE IF EXISTS user_test_ext;<br/>CREATE TABLE user_test_ext (<br/>  id int AUTO_INCREMENT PRIMARY KEY,<br/>  uid int NOT NULL,<br/>  u_password VARCHAR(64) NOT NULL<br/>) ENGINE=InnoDB DEFAULT CHARSET=utf8;<br/>ALTER TABLE user_test_ext ADD INDEX index_user_ext(uid);

Index‑based join sorting:

SELECT user_name, city, age FROM user_test u LEFT JOIN user_test_ext ue ON u.id = ue.uid ORDER BY u.user_name;

Non‑index join sorting:

SELECT user_name, city, age FROM user_test u LEFT JOIN user_test_ext ue ON u.id = ue.uid ORDER BY ue.uid;

Conclusion

The article covered B‑Tree index rules, various index creation methods, and practical tips for building efficient indexes to improve query speed; further optimization techniques require continuous learning and experience.