MySQL indexes are auxiliary data structures that accelerate row retrieval by minimizing scan scope. Rather than reading every row, the storage engine traverses the index to locate qualifying records quickly, trading additional disk space and write-time maintenance for faster reads.

Index Categories

The InnoDB and MyISAM engines support several index classifications, each enforcing different constraints:

• Standard Index: Improves lookup speed without imposing uniqueness rules.
• Unique Index: Guarantees distinct values across the indexed column while allowing exactly one NULL entry.
• Primary Key Index: A specialized unique index that prohibits NULL values and limits the table to a single primary key.
• Composite Index: Spans multiple columns; effectiveness depends on the leftmost prefix rule.
• Full-Text Index: Tokenizes textual content to enable keyword searching via MATCH ... AGAINST.

Sample Table Setup

The examples below reference a table named employees:

CREATE TABLE employees (
    emp_id INT,
    emp_name VARCHAR(32),
    dept VARCHAR(32),
    salary INT,
    biography TEXT
);

Standard Indexes

A standard (non-unique) index places no restrictions on duplicate values.

Explicit creation:

CREATE INDEX idx_name ON employees (emp_name(10));

The trailing number denotes the prefix length in bytes. For CHAR and VARCHAR, this may be shorter than the full column width. For BLOB and TEXT, a prefix is mandatory.

Adding via ALTER TABLE:

ALTER TABLE employees ADD INDEX idx_dept (dept(10));

If the index name is omitted, MySQL assigns a generated name such as dept or dept_2.

Inline table definition:

CREATE TABLE employees (
    emp_id INT,
    emp_name VARCHAR(32),
    dept VARCHAR(32),
    salary INT,
    biography TEXT,
    INDEX idx_name (emp_name(10))
);

Unique Indexes

Unique indexes prevent duplicate entries while permitting a single NULL.

Direct creation:

CREATE UNIQUE INDEX uq_emp_name ON employees (emp_name(20));

Via table alteration:

ALTER TABLE employees ADD UNIQUE uq_dept_name (dept, emp_name);

During table creation:

CREATE TABLE employees (
    emp_id INT,
    emp_name VARCHAR(32),
    dept VARCHAR(32),
    salary INT,
    biography TEXT,
    UNIQUE uq_name (emp_name(10))
);

Primary Key Indexes

A table may contain only one primary key. It implicitly enforces uniqueness and disallows NULL values. A primary key is a unique index, but it cannot simultaneously behave as a full-text index.

Assigning to an existing table:

ALTER TABLE employees ADD PRIMARY KEY (emp_id);

Declaring at creation time:

CREATE TABLE employees (
    emp_id INT,
    emp_name VARCHAR(32),
    dept VARCHAR(32),
    salary INT,
    biography TEXT,
    PRIMARY KEY (emp_id)
);

Composite Indexes

Composite indexes key on multiple columns. The optimizer considers the index only when the query filters by the leftmost column first, followed by subsequent columns in order.

ALTER TABLE employees ADD INDEX idx_dept_name (dept, emp_name);

A predicate on dept alone, or on both dept and emp_name, can leverage this structure. A predicate solely on emp_name cannot.

Full-Text Indexes

Full-text indexes facilitate natural language searches against character-based data. They are invoked with MATCH ... AGAINST rather than LIKE or equality operators. Eligible column types are CHAR, VARCHAR, and TEXT.

When importing large datasets, inserting rows into a table that has no full-text index and then building the index with CREATE FULLTEXT INDEX is typically faster than inserting into an already-indexed table.

Standalone creation:

CREATE FULLTEXT INDEX ft_bio ON employees (biography);

Adding via ALTER TABLE:

ALTER TABLE employees ADD FULLTEXT INDEX ft_dept (dept);

Inline definition:

CREATE TABLE employees (
    emp_id INT,
    emp_name VARCHAR(32),
    dept VARCHAR(32),
    salary INT,
    biography TEXT,
    FULLTEXT ft_bio (biography)
);

Viewing and Dropping Indexes

Inspect metadata:

SHOW CREATE TABLE employees;
SHOW INDEX FROM employees \G

Remove indexes:

DROP INDEX idx_name ON employees;
ALTER TABLE employees DROP PRIMARY KEY;

Diagnostic Utilities

Frequently used statements for schema inspection and timing analysis:

-- Review column definitions
DESC employees;

-- Inspect DDL
SHOW CREATE TABLE employees;

-- List indexes
SHOW INDEX FROM employees;

-- Measure execution duration
SET profiling = 1;
SELECT * FROM employees WHERE emp_name = 'Alice';
SHOW PROFILES;

Scenarios That Suppress Index Usage

Even when present, an index may be ignored if the predicate structure prevents the optimizer from applying it.

Leading wildcard patterns:

SELECT * FROM employees WHERE emp_name LIKE '%son';

Function-wrapped columns:

SELECT * FROM employees WHERE LOWER(emp_name) = 'alice';

Disjunctive OR with unindexed terms:

-- Index not used because biography lacks an index
SELECT * FROM employees WHERE emp_id = 5 OR biography = 'Senior Engineer';

-- May use indexes if both columns are indexed
SELECT * FROM employees WHERE emp_id = 5 OR emp_name = 'Alice';

Implicit type conversion:

Comparing a string column against a numeric literal forces casting and invalidates the index.

-- Avoid: index on emp_name is bypassed
SELECT * FROM employees WHERE emp_name = 404;

Inequality filters:

-- Non-primary key inequality skips the index
SELECT * FROM employees WHERE emp_name != 'Alice';

-- Primary key inequality may still use the index
SELECT * FROM employees WHERE emp_id != 100;

Range predicates on strings:

-- String range generally suppresses index usage
SELECT * FROM employees WHERE emp_name > 'Alice';

-- Integer primary key or indexed integer range often remains effective
SELECT * FROM employees WHERE emp_id > 100;

Ordering by indexed columns while selecting non-indexed projections:

-- Sorting by emp_name but projecting salary may avoid the index
SELECT salary FROM employees ORDER BY emp_name DESC;

-- Sorting by primary key typically retains index utilization
SELECT * FROM employees ORDER BY emp_id DESC;

Composite index left-prefix violations:

Given an index on (dept, emp_name):

-- Uses index: includes leading column
SELECT * FROM employees WHERE dept = 'Engineering' AND emp_name = 'Bob';

-- Uses index: leading column alone
SELECT * FROM employees WHERE dept = 'Engineering';

-- Does not use index: leading column omitted
SELECT * FROM employees WHERE emp_name = 'Bob';

Design Recommendations

• Project only necessary columns instead of issuing unqualified SELECT *.
• Prefer COUNT(1) or COUNT(column) when counting specific non-NULL rows.
• Choose CHAR over VARCHAR for fixed-length data to reduce row fragmentation.
• Define fixed-width columns before variable-width columns in table schemas.
• Consolidate multiple single-column indexes into composite indexes when filters frequently appear together.
• Keep prefix lengths short to minimize index size and insertion overhead.
• Replace correlated subqueries with JOIN operations where semantically equivalent.
• Ensure joined columns share identical data types to prevent implicit conversions.
• Avoid indexing low-cardinality columns such as gender or boolean flags because selectivity is poor.

Pagination Strategies

Offset-based LIMIT clauses degrade on large tables because the engine scans and discards rows preceding the offset. Cursor-based pagination keyed to an indexed identifier scales more efficiently.

Next and previous page navigation:

-- Next page: seek forward from last seen ID
SELECT * FROM employees WHERE emp_id > 5000 ORDER BY emp_id LIMIT 10;

-- Previous page: seek backward from first seen ID
SELECT * FROM employees WHERE emp_id < 4980 ORDER BY emp_id DESC LIMIT 10;

Arbitrary page jumps:

SELECT * FROM employees WHERE emp_id IN (
    SELECT emp_id FROM (
        SELECT emp_id FROM employees WHERE emp_id > 5000 ORDER BY emp_id LIMIT 30
    ) AS page_ids ORDER BY emp_id DESC LIMIT 10
);

Slow Query Log Configuration

MySQL can automatically capture expensive or unindexed statements for offline review.

Variable	Purpose
slow_query_log	Toggles slow query logging on or off.
long_query_time	Threshold in seconds; queries exceeding this value are recorded.
slow_query_log_file	Destination path for the log file.
log_queries_not_using_indexes	Captures queries that perform full table scans evenif they finish quickly.

Review runtime values:

SHOW VARIABLES LIKE '%query%';

Modify dynamically:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

Log analysis:

mysqldumpslow -s at -a /var/lib/mysql/hostname-slow.log

Changes made in option files require a server restart to take effect.