MySQL Indexes from Scratch: What They Are, Why They Matter, and Hands‑On Performance Tests

What: Index definition

MySQL index works like a book’s table of contents: it stores an ordered mapping from column values to the physical row address, allowing the engine to jump directly to matching rows instead of scanning the whole table. The default index type is a B+Tree, which is ordered, hierarchical, and optimized for disk storage.

Why: Impact of missing or ineffective indexes

80 % of online slow‑response incidents, timeouts, CPU saturation, and service avalanches are traced to index problems. Specific symptoms include:

When a table grows to tens or hundreds of thousands of rows, query latency can jump from ~10 ms to 1‑3 s, causing widespread timeouts.

Full‑table scans consume large amounts of DB CPU, degrading all workloads on the same instance.

In high‑concurrency environments, slow queries accumulate connections, exhaust the connection pool, and trigger a cascade failure.

Even when an index exists, it may be ineffective—90 % of beginner mistakes fall into this category.

The core value of an index is to trade a tiny amount of disk space and write overhead for dramatic query‑performance gains, thereby stabilizing the system.

Where: Scenarios for creating or avoiding indexes

Create an index when

Columns are used in WHERE filters (e.g., phone number, order number, user ID, status).

Columns serve as foreign‑key join fields.

Columns appear in ORDER BY for sorting or pagination.

Columns are frequently used in GROUP BY or DISTINCT.

Avoid an index when

The table contains fewer than ~1,000 rows—full scan is faster.

The column is updated or deleted extremely often but queried rarely—index adds write overhead.

The column has very low cardinality (e.g., gender, status 0/1)—index provides little selectivity.

The column changes frequently—each modification must update the index tree, hurting performance.

How: Hands‑on implementation (SpringBoot + MySQL)

Step 1: Create test table

CREATE TABLE `user_order` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'Primary key ID',
  `order_no` varchar(32) NOT NULL COMMENT 'Order number',
  `user_id` bigint NOT NULL COMMENT 'User ID',
  `status` tinyint NOT NULL DEFAULT '0' COMMENT 'Order status 0‑pending 1‑paid 2‑canceled',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Order test table';

Step 2: Bulk‑insert 100 k rows

@SpringBootTest
public class IndexTest {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    // Batch insert 100k rows
    @Test
    public void batchInsertOrder() {
        String sql = "INSERT INTO user_order(order_no,user_id,status) VALUES (?,?,?)";
        List<Object[]> batchList = new ArrayList<>();
        Random random = new Random();
        for (int i = 1; i <= 100000; i++) {
            String orderNo = "ORD" + System.currentTimeMillis() + i;
            long userId = random.nextLong(10000);
            int status = random.nextInt(3);
            batchList.add(new Object[]{orderNo, userId, status});
            if (batchList.size() >= 1000) {
                jdbcTemplate.batchUpdate(sql, batchList);
                batchList.clear();
            }
        }
        if (!batchList.isEmpty()) {
            jdbcTemplate.batchUpdate(sql, batchList);
        }
        System.out.println("100k test rows inserted");
    }
}

Step 3: Query without index (full scan)

-- No‑index query, very slow on 100k rows
SELECT * FROM user_order WHERE order_no = 'ORD17234567891239999';

Measured latency: 80 ~ 200 ms; larger data sets become slower. EXPLAIN shows type = ALL (full scan).

Step 4: Create single‑column index

-- Create index on high‑frequency query column order_no
CREATE INDEX idx_order_no ON user_order(order_no);

Step 5: Re‑run the query after indexing

SELECT * FROM user_order WHERE order_no = 'ORD17234567891239999';

Latency drops to 0 ~ 5 ms (tens‑fold improvement). EXPLAIN now shows type = ref, indicating the index is used.

Step 6: Composite index for frequent business query

Scenario: queries often filter by user_id + status.

-- Create composite index following the left‑most rule
CREATE INDEX idx_user_status ON user_order(user_id, status);

Effective SQL (matches left‑most rule):

SELECT * FROM user_order WHERE user_id = 1001 AND status = 1;
SELECT * FROM user_order WHERE user_id = 1001;

Ineffective SQL (violates left‑most rule, skips the first column):

SELECT * FROM user_order WHERE status = 1;

Common high‑frequency pitfalls (90 % of beginners)

Composite index fails if the left‑most column is omitted; the index becomes useless.

Applying functions or arithmetic to indexed columns (e.g., WHERE DATE(create_time) = '2025-01-01') disables the index.

Passing numeric literals to VARCHAR columns causes implicit conversion and index loss.

Low‑cardinality columns (status, gender) provide little selectivity; indexing them can degrade performance.

Over‑indexing tables with many writes: each INSERT/UPDATE/DELETE must maintain every index, slowing DML.

Deep pagination with large OFFSET forces the optimizer to skip the index and perform a full scan.

Core cheat‑sheet

Index = book catalog; purpose is to avoid full scans and accelerate queries.

Build indexes on columns that are filtered, joined, sorted, or grouped frequently; avoid them on tiny tables, low‑cardinality fields, or columns that change often.

Composite indexes must obey the left‑most matching principle.

Never apply functions, calculations, or implicit type conversions on indexed columns.

First step in online performance tuning: run EXPLAIN to verify whether an index is being used.