Creating Indexes in MySQL

Sample Table Definition

DROP TABLE IF EXISTS tag_info;
CREATE TABLE tag_info (
    rec_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Record ID',
    creator VARCHAR(64) DEFAULT '' COMMENT 'Creator',
    create_ts DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
    updater VARCHAR(64) DEFAULT '' COMMENT 'Updater',
    update_ts DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update time',
    is_removed BIT(1) NOT NULL DEFAULT b'0' COMMENT 'Deleted flag',
    tenant_ref BIGINT NOT NULL DEFAULT 0 COMMENT 'Tenant ID',
    tag_title VARCHAR(250) COMMENT 'Tag name',
    PRIMARY KEY (rec_id)
) COMMENT='Tag mapping table';

Adding an Index via CREATE Statement

CREATE INDEX tag_title_idx ON tag_info (tag_title(250));

Parameters:

• tag_title_idx: Name assigned to the new index.
• tag_info: Target table.
• tag_title(250): Column and optional prefix length for indexing long values.
• Optional ASC/DESC specifies sort order; default is ascending.

Defining Index in Table Creation DDL

CREATE TABLE sample_table (
    col_a INT(8) NOT NULL,
    col_b VARCHAR(50),
    INDEX my_col_idx (col_a)
);

Useful when indexes are predetermined during schema design.

Regardless of method, created indexes share the same underlying structure. Verify with:

SHOW INDEX FROM tag_info;

Key columns in result set:

• Table: Table to which index belongs.
• Non_unique: 0 if unique, 1 otherwise.
• Key_name: Index identifier.
• Seq_in_index: Position within composite index.
• Column_name: Column used for index.
• Collation: A for sorted, NULL for unsorted storage.
• Cardinality: Approximate count of distinct indexed values.
• Sub_part: Prefix length used; NULL means entire column.
• Packed: Compression method; NULL indicates none.
• Null: YES if indexed column permits NULLs.
• Index_type: Structure type (BTREE, HASH, FULLTEXT, RTREE).
• Comment: Additional remarks defined at creation.

Removing an Index

DROP INDEX tag_title_idx ON tag_info;

Specifying Index Structure

By default MySQL uses BTREE. To use HASH:

CREATE INDEX tag_title_idx ON tag_info (tag_title(250)) USING HASH;

Composite Index Usage

Example Table

CREATE TABLE staff_data (
    rec_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    full_name VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'Full name',
    years INT NOT NULL DEFAULT 0 COMMENT 'Age',
    role VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'Position',
    join_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Hire time',
    PRIMARY KEY (rec_id),
    KEY name_age_role_idx (full_name, years, role) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Staff records';

INSERT INTO staff_data(full_name, years, role, join_ts) 
VALUES ('Zhang San', 22, 'manager', NOW());

INSERT INTO staff_data(full_name, years, role, join_ts) 
VALUES ('Li Si', 23, 'dev', NOW());

INSERT INTO staff_data(full_name, years, role, join_ts) 
VALUES ('Wang Wu', 23, 'dev', NOW());

Query Using Index

EXPLAIN SELECT full_name, years 
FROM staff_data 
WHERE full_name = 'Li Si' AND years = 23 AND role = 'manager';
-- key = name_age_role_idx indicates index usage

Pattern Matching and Index Use

• Ineffective: EXPLAIN SELECT * FROM staff_data WHERE full_name LIKE '%Si';
• Effective prefix match: EXPLAIN SELECT * FROM staff_data WHERE full_name LIKE 'Li%';
• Covering index scenario: EXPLAIN SELECT full_name, years, role FROM staff_data WHERE full_name LIKE '%Wu%';

EXPLAIN Output Fields

• id: Identifier of query block; larger means higher priority.
• select_type: Query form (SIMPLE, PRIMARY, SUBQUERY, etc.).
• table: Referenced table.
• type: Join efficiency ranking from best (system, const, eq_ref) to worst (ALL).
• possible_keys: Candidate indexes for optimization.
• key: Actual index chosen.
• key_len: Bytes used in chosen key; shorter without precision loss is better.
• ref: Columns/constants matched against index.
• rows: Estimated rows examined; lower is preferable.
• filtered: Percentage of rows passing WHERE filter.
• Extra: Additional execution notes (Using where, Using index, etc.).

Bulk Data Insertion Procedure

DROP PROCEDURE IF EXISTS populate_staff;
DELIMITER $$
CREATE PROCEDURE populate_staff()
BEGIN
    DECLARE counter INT DEFAULT 1;
    WHILE counter <= 100000 DO
        INSERT INTO staff_data(full_name, years, role)
        VALUES (CONCAT('Emp', counter), counter, 'dev');
        SET counter = counter + 1;
    END WHILE;
END$$
DELIMITER ;
CALL populate_staff();

This procedure inserts 100,000 rows, generating full_name dynamically.

Forcing Index Usage in Queries

EXPLAIN SELECT * FROM staff_data FORCE INDEX(name_age_role_idx)
WHERE full_name < 'LiLei' AND years = 23 AND role = 'dev';

EXPLAIN SELECT * FROM staff_data
WHERE full_name < 'LiLei' AND years = 23 AND role = 'dev';