Bulk Data Insert Optimization

Three key strategies for efficient bulk loading:

• Data Ordering: Since InnoDB tables store data in primary key order, sorting import data by primary key sequence significantly improves load performance.
• Disable Unique Validation: Turn off constraint checking for unique columns during insertion.

SET UNIQUE_CHECKS=0;
SET UNIQUE_CHECKS=1;

• Manual Transaction Control: Disable auto-commit to batch inserts manually.

SET AUTOCOMMIT=0;
SET AUTOCOMMIT=1;

Insert Statement Optimization

Three optimization approaches:

• Multi-Value Inserts: Use batch insert syntax with multiple value sets.

INSERT INTO user_records VALUES(1,'Alice'),(2,'Bob'),(3,'Charlie');

• Transaction Wrapping: Enclose multiple inserts within a transaction.

START TRANSACTION;
INSERT INTO user_records VALUES(1,'Alice');
INSERT INTO user_records VALUES(2,'Bob');
INSERT INTO user_records VALUES(3,'Charlie');
COMMIT;

• Sequential Insertion: Maintain ascending ID order for better performance.

INSERT INTO user_records VALUES(4,'David');
INSERT INTO user_records VALUES(5,'Eve');

Order By Query Optimization

Sample Table Setup:

CREATE TABLE staff (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    salary INT,
    INDEX idx_staff_age_salary (age, salary)
) ENGINE=InnoDB;


INSERT INTO staff VALUES
(1,'Alice',25,2300),(2,'Bob',30,3500),(3,'Carol',25,2800),
(4,'Dave',36,3500),(5,'Eve',21,2200),(6,'Frank',31,3300),
(7,'Grace',26,2700),(8,'Helen',33,3500),(9,'Ivan',23,2400),
(10,'Julia',32,3100),(11,'Kevin',26,2900),(12,'Laura',37,4500);

Sorting Strategy

MySQL prioritizes using index-based retrieval for ORDER BY operations. The optimizer resorts to filesort only when index access is impractical.

When Filesort Gets Triggered

When a query selects columns beyond the indexed columns, MySQL evaluates whether full table scan with sorting might be cheaper than index traversal followed by row lookups. If the SELECT clause only includes indexed columns, the index is utilized directly.

Example 1: Filesort Execution

EXPLAIN SELECT * FROM staff ORDER BY age, salary;

Example 2: Index-Based Sorting

EXPLAIN SELECT id, age, salary FROM staff ORDER BY age, salary;
EXPLAIN SELECT id, age FROM staff ORDER BY age;

These queries leverage covering indexes, retrieving data directly from the index structure.

Multi-Column Sorting Rules

Rule 1: All columns in ORDER BY must use the same direction (all ASC or all DESC).

Rule 2: Column sequence must match the composite index definition order.

EXPLAIN SELECT id, age, salary FROM staff ORDER BY age DESC, salary DESC;
EXPLAIN SELECT id, age, salary FROM staff ORDER BY age DESC, salary ASC;

The first query uses index ordering; the second requires filesort due to mixed sort directions.

Optimizing Filesort Scenarios

Two-Scan Algorithm: Ideal for smaller result sets. The process retrieves sort keys and row pointers, sorts them in sort_buffer, then performs secondary table lookups using the pointers—incurring random I/O overhead.

Single-Scan Algorithm: Retrieves all required columns in one pass, sorts in memory, and outputs directly. Higher memory consumption but superior performance.

Algorithm Selection Logic

MySQL compares the max_length_for_sort_data system variable against total query result size to determine which algorithm to employ. Higher thresholds favor single-scan approach.

Practical Tuning: Increasing sort_buffer_size and max_length_for_sort_data variables encourages the optimizer to choose single-scan algorithms.

Group By Query Optimization

GROUP BY operations inherently perform sorting, with additional grouping steps beyond what ORDER BY does. Like ORDER BY, GROUP BY can leverage index structures for efficiency.

Example: When sorting isn't required, adding ORDER BY NULL eliminates unnecessary sorting overhead.

Key Insight: Creating indexes on queried columns substantially improves GROUP BY performance.

Replacing Subqueries with Joins

Subqueries often perform poorly compared to equivalent join operations.

Subquery Approach: Shows index types of ALL, eq_ref, and index.

Join Approach: Demonstrates index types of ALL and ref.

Join-based queries typically deliver better execution plans and reduced query times.

OR Condition Optimization

For OR clauses to utilize indexes effectively, every condition column must have a usable index. If any colum lacks index coverage, the entire OR operation bypasses index usage.

For composite indexes, the leftmost prefix rule must be satisfied.

EXPLAIN SELECT * FROM staff WHERE id = 1 OR age = 30;
EXPLAIN SELECT * FROM staff WHERE id = 10 OR age = 32 OR salary = 2300;

The first query uses both primary key and index. The second query fails to leverage the composite index on (age, salary) because salary violates the leftmost prefix principle.

UNION as OR Alternative

Modern MySQL versions automatically convert OR to UNION, but explicit rewriting often yields better plans.

EXPLAIN SELECT * FROM staff WHERE id = 10 UNION SELECT * FROM staff WHERE age = 32;

UNION typically produces more efficient access types (const, ref) compared to OR's range scans.

Access Type Hierarchy (best to worst): system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

Pagination Query Optimization

As OFFSET increases, LIMIT queries degrade significantly. Fetching 10 records from position 2,000,000 still sorts and discards 2 million rows.

SELECT * FROM products LIMIT 2000000, 10;

Optimization Approach 1: Index-Based Pagination with Join

Sort and paginate on the index column first, then join back to retrieve full rows.

SELECT p.* FROM products p
INNER JOIN (SELECT id FROM products ORDER BY id LIMIT 2000000, 10) AS offset_ids
ON p.id = offset_ids.id;

This leverages primary key ordering for efficient subset selection.

Optimization Approach 2: Seek Method

Suitable for tables with auto-incrementing primary keys and no deleted rows.

SELECT * FROM products WHERE id > 1000000 LIMIT 10;

Directly seeks from a known position using index-ordered IDs. Requires continuous ID sequence without gaps.

SQL Hints for Manual Index Control

SELECT * FROM staff USE INDEX (idx_staff_age_salary) WHERE name = 'Alice';

Hint	Function	Behavior
USE INDEX	Provides index candidates for MySQL to consider	Advisory only
IGNORE INDEX	Excludes specific indexes from consideration	Directive
FORCE INDEX	Forces usage of specified index	Mandatory

These hints guide the optimizer when automatic selection produces suboptimal plans.