Master MySQL Index Optimization: From Full Table Scans to Covering Indexes

The article begins by contrasting NoSQL (MongoDB) and relational (MySQL) databases, noting MySQL’s strong consistency and indexing capabilities but also its tendency toward slow queries on large tables when indexes are missing or ineffective.

Scenario 1: Order Import – Duplicate Detection

Business logic checks for existing orders by transaction_id. The initial query performs a full table scan ( type = ALL) and is slow on millions of rows. Using EXPLAIN reveals no index usage (null key, possible_keys).

Solution: create a unique index on transaction_id:

create unique index idx_order_transaID on itdragon_order_list (transaction_id);

After the index, EXPLAIN shows type = const, meaning the row is found directly via the index.

Covering Index

Changing the query to select only transaction_id yields Extra = Using index, indicating a covering index that avoids accessing the table rows.

Scenario 2: Order Management Page – Sorting by Level and Date

The original query orders by order_level, input_date and triggers Using filesort with a full table scan. A composite index is created:

create index idx_order_levelDate on itdragon_order_list (order_level, input_date);

However, EXPLAIN still reports type = ALL and Using filesort, indicating the index was not used (possible index loss or query shape).

Switching to SELECT order_level, input_date ... changes type to index and shows Using index, confirming a covering index, but returns only two columns, which may not satisfy business needs.

To force the optimizer to use the composite index while still selecting all columns, the FORCE INDEX hint is applied:

SELECT * FROM itdragon_order_list FORCE INDEX(idx_order_levelDate) ORDER BY order_level, input_date;

Further analysis shows that ordering solely by input_date after fixing order_level (e.g., filtering on a specific level) yields a better plan: type = ref, shorter key_len, and Extra = Using index condition, indicating the optimizer can decide between index or full scan based on row count.

Index Basics

Definitions, advantages (faster retrieval, reduced sorting cost) and disadvantages (storage overhead, slower writes, maintenance complexity) are listed. Types of indexes (single‑column, unique, composite) and best practices (limit to ~5 indexes per table) are discussed.

SQL syntax for creating, altering, dropping, and showing indexes is provided.

When to Create or Avoid Indexes

Create on primary/unique keys, frequently queried columns, columns used for sorting/grouping, and foreign‑key columns.

Avoid on very small tables, high‑write tables, low‑cardinality columns (e.g., boolean), frequently updated columns, and columns not used in WHERE clauses.

EXPLAIN Output Fields

Each column ( id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra) is explained with its impact on performance. The hierarchy of type values from best to worst is highlighted: system > const > eq_ref > ref > range > index > all.

Common Performance Bottlenecks

MySQL hardware limits (CPU saturation, disk I/O, insufficient memory) and server configuration issues are noted.

Summary

Indexes are ordered data structures that accelerate lookups but slow writes.

Missing or ineffective indexes are a primary cause of query slowdown.

Use EXPLAIN to diagnose query plans and identify full scans, filesorts, or temporary tables.

Prefer composite indexes that cover the query’s WHERE and ORDER BY columns.

Avoid over‑indexing; balance read performance with write overhead.

Consider table redesign (archiving, denormalization) to reduce joins.

By following these steps, developers can transform slow MySQL queries into efficient, index‑driven operations suitable for million‑row order tables.