MySQL Large Table Optimization: 5 Strategies for 100M+ Row Queries

Problem: Queries Slow Down as Table Grows Beyond 100 Million Rows

A historical load table ( history_load) approached 200 million rows. Queries that once took 0.1 seconds now take 3 seconds; COUNT(*) times out; index creation runs for hours; backup and recovery take days. The root cause is not missing indexes or bad SQL — the data volume itself exceeds the optimal capacity of a single MySQL table. Beyond tens of millions of rows, B+ tree index depth increases and data pages swap frequently, causing significant performance degradation.

Strategy 1: Index Optimization (Lowest Cost, Highest Impact)

Core Principle: Make Queries Scan Less Data

1. Create Appropriate Indexes

Index columns used in WHERE, ORDER BY, and JOIN. For a load history table frequently queried by device_code and collect_time:

CREATE INDEX idx_device_time ON history_load (device_code, collect_time DESC);

2. Follow the Leftmost Prefix Rule

A composite index (a, b, c) only works when the query includes column a. Queries filtering only on b and c cannot use the index.

3. Avoid Index Invalidation

Do not apply functions on indexed columns: WHERE DATE(collect_time) = '2026-07-25' → rewrite as a range query.

Do not trigger implicit type conversion: passing a number to a varchar column without quotes invalidates the index.

Avoid LIKE '%xxx' leading wildcard patterns.

4. Use Covering Indexes

If a query only needs a few columns, include them in the index to avoid table lookups:

-- Only querying id and load_value, create covering index
CREATE INDEX idx_cover ON history_load (device_code, collect_time, load_value);

5. Analyze Execution Plans with EXPLAIN

Focus on three fields: type: ALL indicates a full table scan — must optimize. key: NULL means no index was used. rows: estimated rows scanned; smaller is better.

Strategy 2: SQL Rewriting (No Infrastructure Cost)

1. Avoid SELECT * — Select Only Required Columns

SELECT *

inflates result sets, increasing disk I/O and network overhead.

-- Wrong
SELECT * FROM history_load WHERE device_code = 'DEV-1024';

-- Correct
SELECT id, collect_time, load_value FROM history_load WHERE device_code = 'DEV-1024';

2. Replace Subqueries with JOIN

Subqueries create temporary tables and full scans; JOIN can leverage indexes. On 5 million order records, JOIN was 20× faster than an IN subquery.

3. Filter with WHERE Before Aggregation

Reduce the dataset before grouping.

-- Wrong: aggregate then filter
SELECT device_code, AVG(load_value) FROM history_load 
GROUP BY device_code HAVING device_code = 'DEV-1024';

-- Correct: filter then aggregate
SELECT device_code, AVG(load_value) FROM history_load 
WHERE device_code = 'DEV-1024' GROUP BY device_code;

Strategy 3: Pagination Optimization (Solving Deep Pagination)

Deep pagination ( LIMIT 1000000, 20) forces MySQL to scan and discard the first 1 million rows.

Solution 1: Cursor-Based Pagination (Recommended)

Use an ordered unique value as an anchor to avoid traversing historical data.

-- First page
SELECT * FROM history_load 
WHERE device_code = 'DEV-1024'
ORDER BY collect_time DESC, id DESC LIMIT 20;

-- Next page: remember last row's collect_time and id
SELECT * FROM history_load 
WHERE device_code = 'DEV-1024'
AND (collect_time < '2026-07-25 14:23:45'
     OR (collect_time = '2026-07-25 14:23:45' AND id < 123456))
ORDER BY collect_time DESC, id DESC LIMIT 20;

Execution time dropped from 1200 ms to 8 ms; scanned rows from 100,000 to 10.

Solution 2: Covering Index + Subquery

First fetch IDs via covering index, then retrieve full rows by ID.

SELECT * FROM history_load 
WHERE id IN (
    SELECT id FROM history_load 
    WHERE device_code = 'DEV-1024'
    ORDER BY collect_time DESC 
    LIMIT 1000000, 20
);

Performance improves over 10× compared to direct OFFSET.

Strategy 4: Table Structure Optimization

1. Choose Appropriate Data Types

Use INT instead of BIGINT, TINYINT instead of INT when possible. Saves storage and reduces I/O.

2. Vertical Partitioning

Move rarely used columns to a separate table. Example: a raw_data column storing multi-KB raw messages is never needed in list queries — split it out.

3. Partitioned Tables

Partition by time (e.g., monthly) so queries scan only relevant partitions. Keep each partition under 10 million rows to avoid local hotspots.

CREATE TABLE history_load (
    id BIGINT,
    collect_time DATETIME,
    load_value DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(collect_time) * 100 + MONTH(collect_time)) (
    PARTITION p202601 VALUES LESS THAN (202602),
    PARTITION p202602 VALUES LESS THAN (202603)
);

Strategy 5: Architectural Upgrades (High Cost, Fundamental Fix)

Read-Write Separation: Route reporting and historical queries to replicas; primary handles only core transactions.

Introduce Caching (Redis): Serve hot data (e.g., latest device load) from cache.

Cold-Hot Data Separation: Archive historical data to cold storage (object storage, HDFS); keep only recent months in the hot database.

Sharding: Split by device ID or time into multiple tables. Last resort — major architectural change, avoid unless necessary.

Practical Priority Order (Cost vs. Effect)

Priority 1: Index optimization + SQL rewrite — low cost (code change), high effect (10–100× speedup).

Priority 2: Cursor pagination — low cost (SQL change), high effect (100× speedup).

Priority 3: Table structure optimization (data types, vertical split) — medium cost (schema change), medium effect.

Priority 4: Partitioned tables — medium cost (schema change), medium-high effect.

Priority 5: Read-write separation, caching, cold-hot separation — high cost (architecture), high effect.

Priority 6: Sharding — highest cost (re-architecture), highest effect.

Core Principle: The goal of large-table optimization is "make queries scan less data." Indexes and SQL tuning offer the best ROI.

Immediate Action Plan for a 100M+ Row Table

Enable slow query log; identify top 5 slowest queries.

Run EXPLAIN on those queries to analyze execution plans.

Add targeted indexes and rewrite SQL accordingly.

These three steps resolve most performance issues. Only then consider partitioning and architectural changes.

Quick Reference Cheat Sheet

Queries getting slower: Use EXPLAIN; check type and rows → add indexes, rewrite SQL, use covering indexes.

Deep pagination slow: Large LIMIT offset → cursor pagination or subquery with covering index.

COUNT(*) times out: Full table scan on large table → summary tables, Redis counters, approximate estimation.

Table too large: Single table exceeds 100M rows → partitioned tables, cold-hot separation, sharding.