MySQL Performance Tuning - 10M Row Full Table Scan Fix

At some point, every production MySQL database hits a wall. Traffic grows, tables balloon past 50 million rows, and suddenly that dashboard query that used to be instant is timing out. Your users notice before your monitoring does. This isn't bad luck — it's physics. MySQL is doing exactly what you told it to, just with more data than your original design anticipated. The engineers who get promoted are the ones who saw it coming and knew exactly which levers to pull.

What is MySQL Performance Tuning?

Performance tuning means making MySQL use its resources efficiently: memory to cache data, CPU to execute queries fast, and disk only when necessary. The goal is to minimize query latency and maximize throughput. This involves three pillars: indexing strategies, query optimization, and InnoDB configuration.

You'll know you need tuning when a query that worked fine with 1 million rows suddenly crawls past 10 million. The solution is never just "add more hardware" — at least not until you've exhausted your tuning options.

-- TheCodeForge — check current buffer pool hit ratio
SELECT
    (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads)
    / Innodb_buffer_pool_read_requests * 100 AS buffer_pool_hit_ratio
FROM performance_schema.global_status
WHERE variable_name IN ('Innodb_buffer_pool_read_requests', 'Innodb_buffer_pool_reads');

How Indexes Actually Work in InnoDB

Your index is a B+ tree stored away from the table's data rows. Every query that uses an index walks this tree — root to leaf — in log(n) steps. That's why a query scanning 10 million rows with an index might only read 20 tree nodes.

InnoDB uses a clustered index on the primary key — meaning the table data itself is stored in the B+ tree order. Secondary indexes store a copy of the indexed column(s) plus the primary key value, so a lookup via a secondary index requires two tree walks: first to find the primary key, then to the clustered index.

When you add an index, you're trading write overhead for read speed. Each INSERT or UPDATE must update every relevant index. That's why you don't want indexes on columns you never filter or sort by.

-- TheCodeForge — add index and check size
ALTER TABLE orders ADD INDEX idx_customer_id (customer_id);

-- Check index size
SELECT
    table_name,
    index_name,
    stat_value * @@innodb_page_size AS index_size_bytes
FROM mysql.innodb_index_stats
WHERE table_name = 'orders' AND stat_name = 'size';

Reading Execution Plans with EXPLAIN

EXPLAIN shows you how MySQL will execute a query. The key fields are type, key, rows, and Extra. type should ideally be ref, eq_ref, or const — not ALL (full table scan) or index (full index scan).

rows is an estimate — it tells you how many rows MySQL expects to examine. If that number is close to the total table size, you're scanning everything. Extra often reveals hidden work: "Using filesort" means MySQL had to sort in temp memory, "Using temporary" means it created a temp table (often for GROUP BY or DISTINCT).

Always run EXPLAIN on new queries before deployment. Also run it when query performance regresses — the plan can change after table data grows or statistics update.

-- TheCodeForge — analyze a slow query
EXPLAIN FORMAT=JSON
SELECT o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date >= '2025-01-01'
  AND o.total_amount > 100;

-- Look for:
-- "access_type": "ALL" on orders => full scan
-- "possible_keys": NULL => no usable index
-- "rows_examined_per_scan": large => too many rows

Tuning the InnoDB Buffer Pool

The InnoDB buffer pool caches table and index data in memory. When a page is requested, InnoDB checks the buffer pool; if missing, it reads from disk (buffer pool miss). Tuning it is often the second step after indexing.

The buffer pool size should be as large as possible without causing swapping. On a dedicated MySQL server, 70-80% of RAM is a safe starting point. Use innodb_buffer_pool_size to set it, and innodb_buffer_pool_instances to reduce contention on large pools (each instance manages its own buffer chunk).

Monitor hit ratio: you want >99%. A drop below 99% means your active data set no longer fits. Either increase the pool, or query fewer rows per statement.

-- TheCodeForge — find your ideal buffer pool size
SELECT
    CEILING(SUM(data_length + index_length) / 1024 / 1024) AS estimated_mb
FROM information_schema.tables
WHERE table_schema = 'your_database';

Query Optimizer Gotchas

The MySQL query optimizer makes execution plan decisions based on statistics about tables and indexes. If statistics are stale, you'll get a bad plan.

Stale statistics happen after large INSERT, UPDATE, or DELETE operations — especially bulk loads. The optimizer doesn't automatically recompute index cardinality. You must run ANALYZE TABLE.

Another common gotcha: the optimizer may choose not to use an index if it estimates that reading the index plus the rows would be more expensive than a full table scan. This happens for low selectivity or very small tables.

Also, MySQL's optimizer doesn't always handle OR conditions well. Sometimes rewriting with UNION can force a better plan.

-- TheCodeForge — refresh statistics and check plan change
ANALYZE TABLE orders;

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

-- Compare before/after: type, rows, and Extra should improve.

Monitoring and Profiling Queries

You can't fix what you don't measure. Enable the slow query log with long_query_time=2 (captures queries >2 seconds). Use pt-query-digest or mysqldumpslow to analyze the log.

Performance Schema provides fine-grained metrics: wait events, stage events, and statement events. It's enabled by default in MySQL 8.0. Use sys schema to get human-readable summaries.

For real-time profiling, use SHOW PROFILE (deprecated in 8.0 but still works) or the performance_schema. Re-enable it per connection.

Set up automated alerts for query time outliers — don't wait for users to complain.

-- TheCodeForge — enable slow query log
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = ON;

-- View top slow queries
SELECT * FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_TIMER_WAIT > 10000000000  -- >10 seconds
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Configuration Parameters That Impact Performance

Beyond the buffer pool, a handful of MySQL configuration parameters can make or break your production workload. innodb_log_file_size controls the redo log capacity — too small causes frequent checkpoint flushes that stall writes. sort_buffer_size and join_buffer_size are per-session buffers; setting them too high globally can eat memory fast. max_connections needs to balance against thread stack and connection overhead.

The key is knowing which parameters are global and which are session-level. Global changes require a restart, so tune them in a staging environment first. Session-level settings like sort_buffer_size can be set per query with SET SESSION — useful for batch jobs without affecting normal traffic.

-- TheCodeForge — review current configuration
SHOW VARIABLES LIKE 'innodb_log_file_size';
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
SHOW STATUS LIKE 'Innodb_log_waits';  -- nonzero means log contention

Schema Optimization for Performance

Performance doesn't start with queries — it starts with schema design. Using appropriate data types (INT vs BIGINT, CHAR vs VARCHAR) reduces index size and memory pressure. Normalization avoids duplicate data but can cause JOIN overhead; denormalization speeds reads at the cost of write complexity. Choose based on access patterns.

Partitioning large tables can improve query isolation: for example, range-based partitioning on order_date allows partition pruning, scanning only relevant partitions. But too many partitions can degrade DDL performance.

Using covering indexes — where the index includes all columns needed by a query — eliminates the need for a second lookup (the clustered index). This is the fastest access method after primary key lookup.

-- TheCodeForge — create a covering index to avoid back-to-table
-- Query: SELECT customer_id, order_date, total FROM orders WHERE customer_id = 123;
CREATE INDEX idx_customer_covering ON orders (customer_id, order_date, total);

-- Now EXPLAIN shows 'Using index' in Extra — no table access needed.

Advanced Indexing Techniques: Covering Indexes and Index Hints

Covering indexes include all columns a query needs, so MySQL can return results without touching the table at all. EXPLAIN will show 'Using index' in Extra. This avoids the second tree walk from secondary to clustered index, cutting I/O in half for many queries.

Index hints like FORCE INDEX tell the optimizer to use a specific index, but they're a temporary fix. Better to make the optimizer's default choice optimal by maintaining accurate statistics and designing appropriate composite indexes.

MySQL 8.0+ supports functional indexes — index on expressions like YEAR(order_date). Use them when WHERE clauses apply functions to columns. Without a functional index, MySQL can't use a regular index on order_date for a query like WHERE YEAR(order_date) = 2025.

-- TheCodeForge — covering index example
CREATE INDEX idx_covering ON orders (customer_id, total, order_date);

-- Now this query reads only the index:
SELECT customer_id, total, order_date FROM orders WHERE customer_id > 100;

-- Functional index in MySQL 8.0:
CREATE INDEX idx_year_order ON orders ((YEAR(order_date)));

-- Query that benefits:
SELECT * FROM orders WHERE YEAR(order_date) = 2025;

-- Use FORCE INDEX as a last resort:
SELECT * FROM orders FORCE INDEX (idx_customer_id) WHERE customer_id = 42;

InnoDB Write Path: Redo Log, Checkpointing, and Flushing

When you update a row, InnoDB writes to the redo log first (sequential write) before modifying the buffer pool page. The redo log is a circular buffer of log files. A checkpoint writes dirty pages from the buffer pool to disk, advancing the checkpoint LSN.

If the redo log is too small, InnoDB checkpoints aggressively, causing write stalls. Adaptive flushing attempts to spread writes evenly over time, but can still spike under heavy load.

The doublewrite buffer protects against partial page writes — InnoDB writes a page twice before applying to the tablespace. This adds a small write overhead but prevents data corruption on crash.

For write-heavy workloads, tune innodb_log_file_size to avoid checkpoint pressure, and consider innodb_flush_log_at_trx_commit for durability vs performance trade-offs.

-- TheCodeForge — check redo log pressure
SHOW STATUS LIKE 'Innodb_log_waits';  -- >0 indicates log contention

-- Check current log file size
SELECT @@innodb_log_file_size;

-- Check checkpoint age (in bytes)
SELECT variable_value - LSN AS checkpoint_age
FROM performance_schema.global_status
WHERE variable_name = 'Innodb_redo_log_current_lsn';

-- Set doublewrite buffer status
SHOW VARIABLES LIKE 'innodb_doublewrite';

Why Your Indexes Are Lying to You: The Filtered vs. Rows Examined Trap

Most developers think EXPLAIN tells the truth. It doesn't. It gives you an estimate, and those estimates are often garbage. The filtered column in EXPLAIN represents MySQL's guess at what percentage of rows will be returned after applying WHERE conditions. That guess is based on index cardinality statistics, which can be wildly outdated if you haven't run ANALYZE TABLE recently.

Here's the real problem: You see rows=100000, filtered=50 and think "great, only 50,000 rows to scan." But filtered could be 1% in reality, or 99%. MySQL has no idea about data distribution unless you tell it. The only way to know if your index is actually working is to check the actual rows examined in slow query log vs. the rows returned. If the ratio is above 10:1, your index is lying to you.

Never trust filtered alone. Pair it with Handler_read_% status variables. If Handler_read_next is high but rows returned are low, you're scanning more than needed. Consider a covering index or reorganizing your WHERE clause.

// io.thecodeforge — database tutorial

-- Check real index usage vs. estimates
SET SESSION optimizer_trace='enabled=on';

EXPLAIN FORMAT=JSON 
SELECT order_id, total 
FROM orders 
WHERE status = 'shipped' 
  AND created_at > '2024-01-01';

-- Compare with actual execution
SELECT COUNT(*) 
FROM orders 
WHERE status = 'shipped' 
  AND created_at > '2024-01-01';

-- Check Handler status before & after
SHOW STATUS LIKE 'Handler_read_next';

The Query Cache Was Never Your Friend: Why You Should Disable It

The MySQL query cache is a relic from a simpler time when databases served static content. It gave the illusion of free speed by caching result sets, but in write-heavy workloads it became a serialization bottleneck. Every write invalidates all cached queries referencing that table, causing a global lock on cache operations. In production, you'd see periods of high throughput followed by sudden stalls when a write hit a hot table.

Modern MySQL 8.0 removed it entirely. If you're on 5.7 and think you need it, you're wrong. Your time is better spent on redis, memcached, or application-level caching that doesn't trash your InnoDB buffer pool. The query cache also masked real performance problems. If your query was fast only because of caching, you were one server restart away from a disaster.

Set query_cache_type=0 and query_cache_size=0. Then watch your write throughput climb. The buffer pool does a better job of caching hot data pages anyway. If you need result caching, do it at the application layer where you have control over invalidation.

// io.thecodeforge — database tutorial

-- Check current query cache status
SHOW VARIABLES LIKE 'query_cache_%';

-- See if it's causing contention
SHOW STATUS LIKE 'Qcache_%';

-- If Qcache_free_blocks is high -> fragmentation
-- If Qcache_lowmem_prunes is high -> too small
-- If Qcache_hits is high but writes are slow -> you're burning CPU

-- Disable it permanently in my.cnf:
-- query_cache_type = 0
-- query_cache_size = 0

-- Verify after restart
SELECT @@query_cache_type, @@query_cache_size;