Database indexes function as auxiliary data structures designed to accelerate query execution by minimizing the volume of records that require examination during a scan. Without these structures, the storage engine must perform exhaustive full-table evaluations. Leveraging balanced tree implementations (such as B-trees or B+ trees) enables logarithmic time complexity lookups.

Preformance Trade-offs

Implementing indexing introduces a balance between read acceleration and write overhead.

• Read Optimization: Indexes drastically reduce row filtering time. When backed by sorted tree structures, they naturally satisfy GROUP BY and ORDER BY clauses without requiring temporary tables or filesort operations. Unique constraints also enforce data integrity at the storage level.
• Write Overhead: Every INSERT, UPDATE, or DELETE operation necessitates corresponding modifications to the index trees, introducing additional disk I/O. Storage consumption increases proportionally with the number of indexed columns and index depth.

Strategic Index Placement

Effective indexing targets columns with high selectivity. Ideal candidates include primary keys, foreign keys used in JOIN operasions, and fields frequently filtered via WHERE clauses or utilized in range predicates. Conversely, low-cardinality fields (e.g., boolean flags) rarely benefit from indexing, as the optimizer will bypass them in favor of sequential scans. Columns heavily involved in write transactions or containing large unstructured payloads (e.g., extensive TEXT or BLOB data) should generally be excluded to prevent storage bloat and lock contention.

Index Classifications and Implementation

Storage engines support multiple index variants tailored to specific workload requirements.

Standard Index Provides basic traversal acceleration without enfocring uniquenes constraints.

-- Inline definition during table creation
CREATE TABLE system_logs (
  log_id BIGINT UNSIGNED PRIMARY KEY,
  event_source VARCHAR(64),
  INDEX idx_source (event_source)
);

-- Retrospective addition via schema alteration
ALTER TABLE system_logs ADD INDEX idx_created_at (created_at);

Unique Index Guarantees distinct values across rows while permitting multiple NULL entries. Composite uniqueness applies the distinct rule to the combined key tuple.

CREATE TABLE customer_accounts (
  account_uid CHAR(36) NOT NULL,
  email_address VARCHAR(128),
  UNIQUE KEY uq_email (email_address),
  INDEX idx_account (account_uid)
);

-- Or applied post-creation
ALTER TABLE customer_accounts ADD UNIQUE uq_ref_code (referral_code);

Primary Key Index A specialized unique index that disallows NULL values and serves as the foundational row identifier. Most relational databases automatically construct this structure upon table initialization.

ALTER TABLE inventory_items 
  ADD CONSTRAINT pk_item PRIMARY KEY (item_sku);

Full-Text Search Index Designed for lexical pattern matching within textual data, superseding inefficient LIKE '%term%' operations. Tokenization algorithms segment content into searchable terms. Note that building these structures on populated tables can be resource-intensive.

CREATE TABLE knowledge_base (
  doc_id INT AUTO_INCREMENT,
  topic_title VARCHAR(255),
  full_content TEXT,
  PRIMARY KEY (doc_id),
  FULLTEXT KEY ft_search (topic_title, full_content)
);

ALTER TABLE knowledge_base ADD FULLTEXT ft_summary (summary);

Composite (Multi-Column) Index Combines multiple fields into a single tree. The leftmost prefix rule dictates that queries can only utilize the index if they filter starting from the first column defined in the key sequence.

ALTER TABLE user_sessions 
  ADD INDEX idx_geo_activity (region_code, last_active_ts, status_flag);

Metadata and Cleanup Inspecting existing index trees:

SHOW INDEXES FROM user_sessions \G;

Removing obsolete structures:

DROP INDEX idx_geo_activity ON user_sessions;
ALTER TABLE knowledge_base DROP INDEX ft_summary;

Storage Alignment: Clustered vs. Secondary Indexes

The physical arrangement of table records relative to index keys defines the storage model.

Clustered Indexes The table data pages are physically ordered to match the logical sequence of the clustered key. In InnoDB, this is inherently tied to the primary key. If no explicit primary key exists, the engine generates a hidden row identifier. Range scans and sorted operation are highly efficient because adjacent logical keys correspond to contiguous disk blocks. However, modifying the clustered key frequently triggers costly page splits and row migrations, making it unsuitable for volatile update-heavy columns.

Secondary (Non-Clustered) Indexes These structures maintain a logical order independent of physical data placement. Leaf nodes in a secondary index do not store complete rows; instead, they store the index value alongside a pointer to the corresponding clustered index key. The engine performs a second lookup (bookmark lookup) to fetch the actual data. Tables can host numerous secondary indexes, but excessive usage increases write amplification.

Gap Locking and Concurrency Control

Under the REPEATABLE READ isolation level, InnoDB employs gap locks (often combined with record locks as next-key locks) to maintain transaction consistency during range scans.

Phantom Read Mitigation Phantom reads occur when repeated identical range queries within a transaction yield differing row counts due to concurrent insertions by other sessions. To prevent this, the engine locks not only the matched records but also the intervals between index entries within the queried range.

Mechanics

1. When a transaction executes a conditional range filter or SELECT ... FOR UPDATE, the engine acquires shared or exclusive gap locks across the scanned index intervals.
2. These locks block concurrent transactions from inserting new rows into the protected intervals. Any insertion attempt targeting a locked interval is suspended until the holding transaction commits or rolls back.
3. Locks are automatically released upon transaction completion.

While gap locking guarantees strict range consistency and serializable behavior for specific operations, it introduces serialization overhead. High-concurrency environments requiring maximum insert throughput may need to adjust isolation levels or optimize query predicates to minimize locked ranges.