MySQL 9.5 Performance Tuning: 5 Core Techniques to Cut Query Time from 10s to 10ms

Table Structure Design

Database performance depends on table structure, queries, and configuration. Smaller data types reduce disk I/O and increase memory efficiency.

Data Types: Smaller Is Better

TINYINT for status codes : Use TINYINT instead of INT for 0/1 flags; each saved byte reduces future disk I/O.

DATE over DATETIME : If time component is irrelevant, use DATE to avoid unnecessary storage.

Avoid NULL : NULL complicates indexes and calculations; set default values (empty string, 0) instead.

String optimization : Store IP as unsigned integer, UUID as BINARY(16), fixed-length strings as CHAR, variable-length as VARCHAR with appropriate length.

Time types : Use DATE for date-only; TIMESTAMP for auto-updating timestamps.

Key principle : Smaller data types → less disk space → more data in memory → fewer I/O operations → faster speed.

Normalization vs. Denormalization

Normalized (high normal form) : Like separate finances — clear accounts, fast writes. Suits write-heavy, read-light scenarios.

Denormalized (low normal form) : Like shared wallet — convenient reads, avoids JOINs. Suits read-heavy, write-light scenarios.

No absolute right or wrong; choose based on workload. Moderate redundancy for extreme query speed is wise.

Normalized Design Example (3NF)

-- Normalized design example
CREATE TABLE users (
  user_id INT PRIMARY KEY,
  username VARCHAR(50),
  email VARCHAR(100)
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  user_id INT,
  order_date DATE,
  FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE order_items (
  item_id INT PRIMARY KEY,
  order_id INT,
  product_id INT,
  quantity INT,
  FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

Advantages: minimal redundancy, high consistency, storage efficiency. Drawback: excessive JOINs can become bottlenecks.

Denormalization Scenarios

-- Denormalized design: orders table includes user info
CREATE TABLE orders_denormalized (
  order_id INT PRIMARY KEY,
  user_id INT,
  username VARCHAR(50),  -- denormalized field
  email VARCHAR(100),    -- denormalized field
  order_date DATE,
  total_amount DECIMAL(10,2)
);

-- Redundant precomputed field
CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(100),
  price DECIMAL(10,2),
  stock_quantity INT,
  total_sold INT DEFAULT 0  -- denormalized: precomputed field
);

Use when reads far exceed writes. A flowchart in the article illustrates the decision process.

Indexing

Indexes are like a smart B+Tree catalog. MySQL 9.5 introduces new index types and optimizations.

Index Types

-- 1. B-Tree index (default): full-value match, range queries, sorting
CREATE INDEX idx_order_date ON orders(order_date);

-- 2. Hash index: equality lookups, Memory engine
CREATE INDEX idx_hash_user ON users(user_id) USING HASH;

-- 3. Full-text index: text search
CREATE FULLTEXT INDEX idx_product_desc ON products(description);

-- 4. Spatial index: geospatial data
CREATE SPATIAL INDEX idx_location ON locations(coordinates);

-- 5. Composite (multi-column) index
CREATE INDEX idx_user_status_date ON orders(user_id, status, order_date);

-- 6. Prefix index: first N characters of string
CREATE INDEX idx_email_prefix ON users(email(20));

-- 7. Function index (MySQL 8.0+): index expression result
CREATE INDEX idx_lower_username ON users((LOWER(username)));

B+Tree Index Mechanics

The article includes a diagram explaining B+Tree structure (root, branch, leaf nodes) and how it enables efficient range scans and ordered retrieval.

Index Design Best Practices

-- 1. High-selectivity columns first
-- user_id has higher selectivity than status, so user_id comes first
CREATE INDEX idx_user_status ON orders(user_id, status);

-- 2. Consider covering indexes
-- Index contains all columns needed by query, avoids table lookup
CREATE INDEX idx_covering ON orders(order_id, user_id, order_date, total_amount);
-- Query can use covering index
SELECT order_id, user_id, order_date
FROM orders
WHERE user_id = 100 AND order_date > '2024-01-01';

-- 3. Avoid duplicate indexes
-- Redundant because idx_a_b can serve queries on column a
CREATE INDEX idx_a ON table1(a);  -- redundant
CREATE INDEX idx_a_b ON table1(a, b);

-- 4. Regularly analyze index usage
SELECT
  object_schema,
  object_name,
  index_name,
  count_star,
  count_read,
  count_fetch
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
ORDER BY count_star DESC;

-- 5. Drop unused indexes
-- Use sys schema to view unused index statistics
SELECT * FROM sys.schema_unused_indexes;

Key Principles

Leftmost Prefix Rule : Composite index (last_name, first_name) works for WHERE last_name='X' and WHERE last_name='X' AND first_name='Y', but NOT for WHERE first_name='Y' alone.

No Functions on Indexed Columns : WHERE YEAR(create_time)=2023 kills index; rewrite as WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'.

High Selectivity : Don't index low-cardinality columns like gender; index high-cardinality columns like ID number.

Invisible Indexes (MySQL 8.0+)

ALTER TABLE user ADD INDEX idx_email (email);  -- create
ALTER TABLE user ALTER INDEX idx_email INVISIBLE;  -- hide!

Index remains but optimizer ignores it. Observe for a period; if no issues, safely DROP. This is a safety net for production index removal.

JOIN Optimization

JOINs are common bottlenecks.

-- 1. Ensure JOIN columns have indexes
-- Before: no index on JOIN column
SELECT * FROM orders o
JOIN users u ON o.user_id = u.user_id;  -- user_id not indexed

-- After: add indexes
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_user_id ON users(user_id);

-- 2. Small table drives large table
-- Before: large drives small
SELECT * FROM large_table l
JOIN small_table s ON l.key = s.key;

-- After: small drives large (using STRAIGHT_JOIN hint)
SELECT STRAIGHT_JOIN s.*, l.*
FROM small_table s
JOIN large_table l ON s.key = l.key;

-- 3. Avoid Cartesian products
-- Ensure all JOINs have join conditions
SELECT * FROM table1, table2 WHERE table1.id = table2.table1_id;  -- correct

-- 4. Choose appropriate JOIN type
-- Pick INNER JOIN, LEFT JOIN based on data distribution

-- 5. Staged JOIN for large datasets
-- Step 1: filter data
CREATE TEMPORARY TABLE filtered_orders
SELECT order_id, user_id
FROM orders
WHERE order_date > '2024-01-01';

-- Step 2: join with other tables
SELECT fo.*, u.username
FROM filtered_orders fo
JOIN users u ON fo.user_id = u.user_id;

Sorting and Grouping Optimization

Traditional indexes are ascending; reverse scans are inefficient. MySQL now supports true descending indexes:

CREATE INDEX idx_time_desc ON article (create_time DESC);

Ideal for news feeds, timelines.

Common Techniques

-- 1. Use index to avoid sort
-- Before: no index for sorting
SELECT * FROM orders ORDER BY order_date DESC;

-- After: add descending index
CREATE INDEX idx_order_date_desc ON orders(order_date DESC);

-- 2. Reduce sorted data volume
-- Before: sort all columns
SELECT * FROM orders ORDER BY order_date LIMIT 100;

-- After: sort only needed columns
SELECT order_id, order_date, total_amount
FROM orders
ORDER BY order_date
LIMIT 100;

-- 3. Optimize GROUP BY
-- Before: GROUP BY without index
SELECT user_id, COUNT(*)
FROM orders
GROUP BY user_id;

-- After: index on GROUP BY column
CREATE INDEX idx_user_id ON orders(user_id);

-- 4. Use derived tables for complex grouping
-- Before: complex GROUP BY with JOIN
SELECT o.user_id, COUNT(*) as order_count, SUM(oi.quantity) as total_quantity
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.user_id;

-- After: staged processing
WITH order_summary AS (
  SELECT o.order_id, o.user_id, SUM(oi.quantity) as order_quantity
  FROM orders o
  JOIN order_items oi ON o.order_id = oi.order_id
  GROUP BY o.order_id, o.user_id
)
SELECT user_id, COUNT(*) as order_count, SUM(order_quantity) as total_quantity
FROM order_summary
GROUP BY user_id;

Pagination Optimization

Large-offset pagination degrades performance.

-- 1. Traditional pagination problem
-- Larger offset = worse performance
SELECT * FROM orders ORDER BY order_date LIMIT 100000, 20;

-- 2. Keyset (cursor) pagination
-- Remember last row's value instead of offset
SELECT * FROM orders
WHERE order_date > '2024-01-01'
ORDER BY order_date
LIMIT 20;
-- Next page
SELECT * FROM orders
WHERE order_date > 'last_row_date'
ORDER BY order_date
LIMIT 20;

-- 3. Covering index optimization
-- Get IDs via covering index, then fetch data
SELECT o.*
FROM orders o
JOIN (
  SELECT order_id
  FROM orders
  WHERE order_date > '2024-01-01'
  ORDER BY order_date
  LIMIT 100000, 20
) tmp ON o.order_id = tmp.order_id;

-- 4. Partitioned table pagination
-- Use partition to reduce scan range
SELECT * FROM orders PARTITION (p2024_q1)
ORDER BY order_date
LIMIT 100000, 20;

10 Common Index Failure Scenarios

SELECT * : Prevents covering index, forces table lookup, increases data transfer.

Functions/operations on indexed columns : e.g., WHERE DATE(create_time)='2025-01-01' or WHERE age+1=20.

Implicit type conversion : Indexed column is INT, query uses string WHERE age='20'.

LIKE with leading wildcard : WHERE name LIKE '%Zhang' fails; WHERE name LIKE 'Zhang%' works.

OR conditions : If one side lacks index, entire query loses index.

NOT IN / NOT EXISTS : Causes full table scan.

Violating leftmost prefix : Skipping left column or range query in middle breaks composite index.

Tiny tables : Optimizer chooses full scan over index for very small tables.

NULL values in indexed columns : MySQL handles NULL specially; many NULLs degrade index efficiency. Set defaults.

Over-indexing : Too many indexes slow optimizer's choice and may pick wrong index.

Query Analysis and Optimization Strategies

80% of performance issues stem from bad SQL. Always run EXPLAIN after changes.

EXPLAIN Deep Dive

-- Analyze query with EXPLAIN
EXPLAIN FORMAT=JSON
SELECT o.order_id, o.order_date, u.username,
       SUM(oi.quantity * p.price) as total_amount
FROM orders o
JOIN users u ON o.user_id = u.user_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= '2024-01-01'
GROUP BY o.order_id
HAVING total_amount > 1000
ORDER BY o.order_date DESC
LIMIT 10;

Key columns to watch:

id : Query block identifier.

select_type : SIMPLE, PRIMARY, SUBQUERY, etc.

table : Accessed table.

partitions : Matched partitions.

type : Access type (best to worst: system > const > eq_ref > ref > range > index > ALL). ALL means full table scan.

key : Index actually used; NULL means missing index.

possible_keys : Candidate indexes.

key_len : Length of index used.

ref : Columns compared to index.

rows : Estimated rows to examine; lower is better.

filtered : Percentage of rows filtered by condition.

Extra : Critical info — Using filesort (extra sort) or Using temporary (temp table) are performance killers.

Pro tip: Use EXPLAIN FORMAT=JSON for detailed cost analysis.

Subquery Pitfalls and Fixes

Subqueries like

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100)

may become correlated subqueries, executing once per outer row.

Recommended fixes: use EXISTS or JOIN.

SELECT u.* FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 100);

-- Or direct JOIN
SELECT DISTINCT u.* FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100;

Additional Query Optimization Tips

-- 1. Avoid SELECT *, list needed columns
-- Before
SELECT * FROM users WHERE user_id = 100;
-- After
SELECT user_id, username, email FROM users WHERE user_id = 100;

-- 2. Use EXISTS instead of IN for large subquery results
-- Before
SELECT * FROM orders WHERE user_id IN (SELECT user_id FROM users WHERE status = 'active');
-- After
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM users u WHERE u.user_id = o.user_id AND u.status = 'active');

-- 3. Split complex queries into multiple simple ones
-- Combine in application layer

-- 4. Use LIMIT appropriately
-- Before: unnecessary large retrieval
SELECT * FROM orders WHERE order_date > '2024-01-01';
-- After: add LIMIT
SELECT * FROM orders WHERE order_date > '2024-01-01' LIMIT 100;

-- 5. Avoid functions on columns in WHERE
-- Before
SELECT * FROM orders WHERE YEAR(order_date) = 2024;
-- After
SELECT * FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

MySQL 9.5 System-Level Optimization

InnoDB is the default engine; tuning it is critical.

Buffer Pool

The heart of InnoDB. Set innodb_buffer_pool_size to 70-80% of system RAM. A large pool keeps hot data in memory, eliminating disk I/O.

Check if pool is sufficient: SHOW STATUS LIKE 'innodb_buffer_pool_read%'; If Innodb_buffer_pool_reads (disk reads) is much larger than Innodb_buffer_pool_read_requests (total read requests), the pool is too small.

Log System

Redo Log ensures durability. innodb_flush_log_at_trx_commit balances performance and safety:

=1 (default) : Flush to disk on every commit. Safest, slowest. For financial/transactional systems.

=2 : Write to OS cache on commit, flush to disk once per second. Good performance, may lose 1 second of data on crash. Suitable for most business scenarios.

=0 : Write and flush once per second. Best performance, may lose up to 1 second of data. For tolerable loss scenarios (e.g., log collection).

Recommendation: If not strong consistency required, try setting to 2 for significant performance gain.

Common Configuration Template

# InnoDB buffer pool (typically 70-80% of system memory)
innodb_buffer_pool_size = 16G
innodb_buffer_pool_instances = 8  # reduce contention, usually CPU core count

# Log file configuration
innodb_log_file_size = 2G          # larger log files reduce checkpoints
innodb_log_buffer_size = 64M       # log buffer size

# Connection management
max_connections = 500              # adjust per application needs
thread_cache_size = 100            # thread cache size

# Query cache (removed in MySQL 8.0+, use alternatives)
# Use application-level cache or ProxySQL query cache

# Temporary table and sort optimization
tmp_table_size = 256M              # max in-memory temp table size
max_heap_table_size = 256M         # max memory table size
sort_buffer_size = 4M              # sort buffer size

# InnoDB I/O optimization
innodb_flush_method = O_DIRECT     # recommended for Linux
innodb_io_capacity = 2000          # adjust per disk performance
innodb_io_capacity_max = 4000      # max I/O capacity

# Transaction isolation and locks
transaction_isolation = READ-COMMITTED  # recommended for most apps
innodb_lock_wait_timeout = 50     # lock wait timeout

Monitoring and Measurement — No Metrics, No Optimization

MySQL 9.5 enhances monitoring; optimization must be data-driven.

-- 1. Performance Schema for performance monitoring
-- View most time-consuming SQL statements
SELECT
  DIGEST_TEXT,
  COUNT_STAR,
  SUM_TIMER_WAIT/1000000000 as total_sec,
  AVG_TIMER_WAIT/1000000000 as avg_sec,
  MAX_TIMER_WAIT/1000000000 as max_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- 2. sys schema for quick diagnosis
-- View lock waits
SELECT * FROM sys.innodb_lock_waits;

-- View unused indexes
SELECT * FROM sys.schema_unused_indexes;

-- View table statistics
SELECT * FROM sys.schema_table_statistics;

-- 3. Slow query log
-- Enable slow query log
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- queries exceeding 1 second

-- Analyze slow query log
# Using mysqldumpslow tool
mysqldumpslow -s t /var/log/mysql/mysql-slow.log

# Using pt-query-digest tool
pt-query-digest /var/log/mysql/mysql-slow.log

-- 4. Real-time monitoring
-- View current connections and executing statements
SELECT * FROM information_schema.processlist
WHERE COMMAND != 'Sleep'
ORDER BY TIME DESC;

-- View InnoDB status
SHOW ENGINE INNODB STATUS;

-- 5. MySQL Shell performance report
-- MySQL Shell provides advanced diagnostics
\sql
\performance report

Key Monitoring Tools

Slow Query Log : Your "medical record". Enable it, record all SQL exceeding long_query_time (e.g., 0.1 sec).

Performance Schema : MySQL's "real-time monitoring dashboard". Deeply monitors statement execution stages, lock waits, I/O operations — essential for advanced tuning.

sys Schema : "Visualized report library" built on Performance Schema. Provides human-readable views, e.g., SELECT * FROM sys.statements_with_full_table_scans;.

Summary: MySQL Optimization Mastery

Diagnose first, prescribe later : EXPLAIN, slow query log, Performance Schema are your stethoscope and CT scanner.

SQL is king : 90% of performance issues can be solved or mitigated at SQL and index level. Master hash joins, subquery optimization.

Understand InnoDB : Tune Buffer Pool and Redo Log to grasp InnoDB's lifeline.

Leverage new features : Invisible indexes, descending indexes are powerful tools for production ops and performance gains.

Hardware is the foundation : SSD solves I/O bottlenecks; memory alleviates I/O pressure.

Data-driven decisions : Establish monitoring baselines; compare before/after metrics for every optimization to ensure real effectiveness.

Optimization is an endless journey, but following MySQL official documentation and this guide will take you from novice to performance tuning master.