Understanding MySQL Indexes: Types, Creation, and Optimization

MySQL indexes are data structures used by the storage engine to quickly locate records, similar to a book's table of contents, reducing query time from O(n) to O(log n) when properly used.

The main advantages of indexes include reduced data scans, faster ORDER BY and GROUP BY operations, and conversion of random I/O to sequential I/O, while the drawbacks are additional storage overhead and maintenance cost during INSERT/UPDATE/DELETE.

MySQL supports several index types, but the most common are B‑tree and B+‑tree indexes provided by the InnoDB engine. B‑tree is a balanced multi‑way search tree, whereas B+‑tree stores only keys in internal nodes and keeps all data in leaf nodes linked sequentially, improving disk I/O and range query performance.

Key indexing techniques discussed:

Prefix Indexes : Index only the leading characters of long string columns to save space, at the cost of reduced selectivity. Example: ALTER TABLE user ADD INDEX sch_pre3(`school(3)`).

Composite (Multi‑Column) Indexes : Combine multiple columns into a single index, respecting the left‑most prefix rule. Example: ALTER TABLE user ADD INDEX school_age(`school`,`age`).

Clustering Indexes : In InnoDB, the primary key serves as a clustered index, storing rows in the same B+‑tree as the index, which makes inserts efficient for auto‑increment keys.

Covering Indexes : Contain all columns needed by a query, allowing the engine to satisfy the query using only the index without accessing the table.

Redundant or unused indexes should be removed to avoid unnecessary write overhead, such as duplicate primary‑key indexes or separate indexes that are already covered by a composite index.

To inspect index metadata, use commands like SHOW INDEX FROM table_name or SHOW CREATE TABLE table_name. Index size and fragmentation can be examined via the information_schema.TABLES view, for example:

SELECT CONCAT(ROUND(SUM(index_length)/1024/1024,2), ' MB') AS 'Total Index Size' FROM information_schema.TABLES WHERE table_schema='your_db';

Fragmented indexes can be rebuilt with OPTIMIZE TABLE tbl_name or by altering the storage engine.

Overall, proper index selection, creation, and maintenance are essential for high‑performance MySQL databases.