MySQL Query Optimization Techniques

-- Basic EXPLAIN
EXPLAIN SELECT * FROM orders
WHERE customer_id = 42
AND status = 'pending'
ORDER BY created_at DESC;

-- EXPLAIN FORMAT=JSON gives more detail
EXPLAIN FORMAT=JSON SELECT * FROM orders
WHERE customer_id = 42;

-- EXPLAIN ANALYZE actually runs the query and shows real statistics
-- (MySQL 8.0.18+)
EXPLAIN ANALYZE SELECT * FROM orders
WHERE customer_id = 42;

-- Single-column index: good for simple equality lookups
CREATE INDEX idx_customer_id ON orders(customer_id);

-- Composite index: covers multiple WHERE conditions
-- Column order matters! Put most selective column first,
-- or put equality columns before range columns
CREATE INDEX idx_customer_status ON orders(customer_id, status);

-- Covering index: includes all columns needed by the query
-- MySQL can satisfy the query entirely from the index (no table lookup)
CREATE INDEX idx_covering ON orders(customer_id, status, created_at, total);

-- Prefix index for large VARCHAR columns
CREATE INDEX idx_email_prefix ON users(email(20));

-- Unique index: prevents duplicates AND speeds up lookups
CREATE UNIQUE INDEX idx_unique_email ON users(email);

-- Check existing indexes
SHOW INDEX FROM orders;

-- Check if a query uses an index efficiently
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';

-- BAD: Function call prevents index usage
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
SELECT * FROM products WHERE price + 10 > 100;

-- GOOD: Rewrite to allow index usage
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

-- For case-insensitive email: use a case-insensitive collation on the column
-- ALTER TABLE users MODIFY email VARCHAR(255) COLLATE utf8mb4_unicode_ci;
-- Then a simple equality works with the index:
SELECT * FROM users WHERE email = 'user@example.com';

SELECT * FROM products WHERE price > 90;  -- functionally equivalent, uses index

-- Ensure foreign key columns are indexed
-- The orders.customer_id column should have an index
-- MySQL automatically creates one for declared FOREIGN KEYs
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_order_items_order ON order_items(order_id);

-- GOOD JOIN: both join columns are indexed
SELECT
    c.name,
    COUNT(o.id) AS order_count,
    SUM(o.total) AS total_spent
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id  -- indexed on both sides
WHERE o.status = 'completed'
  AND o.created_at >= '2026-01-01'
GROUP BY c.id, c.name
ORDER BY total_spent DESC
LIMIT 10;

-- Avoid joining on expressions or functions
-- BAD:
SELECT * FROM orders o
JOIN order_archive oa ON DATE(o.created_at) = DATE(oa.created_at);

-- GOOD: Compare raw column values
SELECT * FROM orders o
JOIN order_archive oa ON o.created_at = oa.created_at;

-- SLOW: Correlated subquery runs once per row in customers
SELECT c.name
FROM customers c
WHERE (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) > 5;

-- FAST: JOIN + GROUP BY runs once
SELECT c.name
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
HAVING COUNT(o.id) > 5;

-- For NOT IN / NOT EXISTS, use LEFT JOIN ... IS NULL
-- (often faster than NOT IN with large sets)
-- SLOW:
SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);

-- FAST:
SELECT c.*
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

-- Good: Uses index on (status, created_at)
SELECT * FROM orders
WHERE status = 'pending'           -- equality first
  AND created_at >= '2026-01-01'   -- range second
  AND created_at < '2026-04-01';

-- Good: Prefix LIKE uses index
SELECT * FROM users WHERE email LIKE 'mayur%';

-- Bad: Leading wildcard can't use index
-- SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- Good: IN clause
SELECT * FROM products WHERE category_id IN (1, 3, 7, 12);

-- Bad: implicit type conversion (user_id is INT)
-- SELECT * FROM users WHERE user_id = '42';

-- Good: matched types
SELECT * FROM users WHERE user_id = 42;

-- Good: EXISTS is often faster than IN with large subqueries
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id
      AND o.status = 'completed'
);

-- With this index, ORDER BY can be served from the index
CREATE INDEX idx_customer_created ON orders(customer_id, created_at DESC);

-- MySQL can now serve this query entirely from the index
-- (no filesort, no temporary table)
SELECT id, total, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

-- For pagination: use keyset pagination instead of OFFSET for large pages
-- BAD: OFFSET 10000 makes MySQL scan and discard 10000 rows
SELECT * FROM orders ORDER BY id DESC LIMIT 20 OFFSET 10000;

-- GOOD: Keyset pagination - cursor-based, fast regardless of page depth
SELECT * FROM orders
WHERE id < 98765  -- last seen id from previous page
ORDER BY id DESC
LIMIT 20;

-- Add index that covers GROUP BY column and aggregate target
CREATE INDEX idx_status_total ON orders(status, total);

-- Count query: can now be answered from the index alone
SELECT status, COUNT(*) AS cnt, SUM(total) AS revenue
FROM orders
GROUP BY status;

-- Use HAVING to filter groups, not WHERE (WHERE filters rows before grouping)
SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE created_at >= '2026-01-01'   -- filter rows first (uses index)
GROUP BY customer_id
HAVING COUNT(*) >= 3;              -- filter groups after aggregation

-- For dashboard summary stats, consider summary tables
-- updated periodically instead of running heavy aggregations on demand
CREATE TABLE order_daily_summary (
    date DATE PRIMARY KEY,
    order_count INT NOT NULL DEFAULT 0,
    revenue DECIMAL(12,2) NOT NULL DEFAULT 0.00
);

[mysqld]
# Set to 70-80% of server RAM (example: 8GB server)
innodb_buffer_pool_size = 6G

# For SSD storage
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# Enable slow query logging to find problems
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON

# Avoid too many temp tables being written to disk
tmp_table_size = 64M
max_heap_table_size = 64M

# Connection settings
max_connections = 200
wait_timeout = 300
interactive_timeout = 300

-- Find the top slow queries from Performance Schema
SELECT
    digest_text,
    count_star AS executions,
    ROUND(avg_timer_wait / 1e9, 2) AS avg_ms,
    ROUND(sum_timer_wait / 1e9, 2) AS total_ms,
    sum_rows_examined AS rows_examined,
    sum_rows_sent AS rows_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 10;

-- Check tables with no primary key (very slow for joins/updates)
SELECT table_schema, table_name
FROM information_schema.tables t
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
  AND NOT EXISTS (
    SELECT 1 FROM information_schema.statistics s
    WHERE s.table_schema = t.table_schema
      AND s.table_name = t.table_name
      AND s.index_name = 'PRIMARY'
  );

-- Use pt-query-digest (Percona Toolkit) to analyze slow query log
-- pt-query-digest /var/log/mysql/slow.log | head -100

-- Partition a large events table by year
CREATE TABLE events (
    id BIGINT AUTO_INCREMENT,
    user_id INT NOT NULL,
    event_type VARCHAR(50) NOT NULL,
    created_at DATETIME NOT NULL,
    payload JSON,
    PRIMARY KEY (id, created_at)  -- partition key must be in PK
)
PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- Query with partition key: MySQL only scans p2026
SELECT * FROM events
WHERE created_at >= '2026-01-01'
  AND created_at < '2027-01-01'
  AND event_type = 'purchase';

-- Check partition pruning with EXPLAIN PARTITIONS
EXPLAIN PARTITIONS SELECT * FROM events
WHERE created_at >= '2026-01-01';

-- Drop old partition (instant, no row-by-row DELETE)
ALTER TABLE events DROP PARTITION p2023;

-- Problem: You need to filter by year of created_at frequently
-- but YEAR(created_at) in WHERE prevents index use

-- Solution: Virtual generated column + index
ALTER TABLE orders
    ADD COLUMN created_year SMALLINT AS (YEAR(created_at)) VIRTUAL,
    ADD INDEX idx_created_year (created_year);

-- Now this query uses the index!
SELECT * FROM orders WHERE created_year = 2026;

-- JSON field indexing with generated columns
ALTER TABLE products
    ADD COLUMN brand VARCHAR(100) AS (JSON_UNQUOTE(metadata->>'$.brand')) STORED,
    ADD INDEX idx_brand (brand);

SELECT * FROM products WHERE brand = 'Sony';