DML Optimization Techniques

Insert Operations

• Utilize batch inserts rather than single-row insert statements to reduce log overhead.
• Insert data sequentially according to the primary key to minimize page splitting.
• For massive datasets (e.g., millions of rows), use the LOAD DATA INFILE command after enabling the local_infile system variable for high-speed loading.

Update Operations

Optimization relies heavily on the filtering conditions in the WHERE clause. Always prioritize columns with indexes, particularly the primary key.

• Using an Index (Row Lock): In InnoDB, updating via an index applies a row lock. Using the primary key minimizes lookup operations.

-- Row lock is utilized
UPDATE product_inventory SET stock_status = 'active' WHERE item_id = 42;

• Without an Index (Table Lock): If no index is used or the index is invalid, the lock escalates from a row lock to a table lock, blocking other transactions.

-- Table lock is utilized due to lack of index on 'name'
UPDATE product_inventory SET stock_status = 'discontinued' WHERE product_name = 'Legacy Item';

DQL Optimization Techniques

Sorting with ORDER BY

MySQL sorting typically results in either Using filesort or Using index. The latter is significantly more efficient as it retrieves data already in sorted order.

• Using filesort: MySQL extracts required rows into a sort buffer and performs the sort operation there. This occurs when indexes are missing or improperly used.
• Using index: The data is retrieved directly through an ordered index scan, eliminating the need for a separate sorting step.

Scenarios causing Using filesort:

-- Sorting without an index
EXPLAIN SELECT emp_id, age, mobile FROM employees ORDER BY age;

-- Sorting by multiple columns backed only by single-column indexes
EXPLAIN SELECT emp_id, age, mobile FROM employees ORDER BY age, mobile;

Scenarios causing Using index:

-- Single column sorted by a single-column index
EXPLAIN SELECT age FROM employees ORDER BY age;

-- Composite index usage (assuming index on age, mobile)
CREATE INDEX idx_age_mobile ON employees(age, mobile);
EXPLAIN SELECT emp_id, age, mobile FROM employees ORDER BY age, mobile;

Grouping with GROUP BY

Indexing improves grouping efficiency, but queries must adhere to the leftmost prefix rule of the composite index.

-- Without index: Using temporary; Using filesort
EXPLAIN SELECT department, COUNT(*) FROM employees GROUP BY department;

-- With composite index (department, age)
CREATE INDEX idx_dept_age ON employees(department, age);

-- Using index (matches leftmost prefix)
EXPLAIN SELECT department, COUNT(*) FROM employees GROUP BY department;

-- Using index; Using temporary (skips the leftmost 'department')
EXPLAIN SELECT age, COUNT(*) FROM employees GROUP BY age;

Pagination with LIMIT

Performance degrades as the offset increases because the database must scan and discard all preceding rows.

-- Fast: Low offset
SELECT * FROM product_log LIMIT 0, 10;

-- Slow: High offset
SELECT * FROM product_log LIMIT 1000000, 10;

Optimization Strategy: Use a covering index combined with a subquery to first locate the IDs, then join back to the original table.

EXPLAIN SELECT p.* 
FROM product_log p 
INNER JOIN (SELECT log_id FROM product_log ORDER BY log_id LIMIT 1000000, 10) AS tmp 
ON p.log_id = tmp.log_id;

Aggregation with COUNT

Performance ranking for count operations is generally as follows:

count(column_name) < count(primary_key) < count(1) ≈ count(*)

It is recommended to use count(*) for standard row counting as it is well-optimized by the parser.

Primary Key Design Strategies

• Minimize the length of the primary key to reduce storage overhead in secondary indexes.
• Prefer sequential data insertion; use AUTO_INCREMENT or similar mechanisms.
• Avoid using random strings like UUIDs or long identifiers like social security numbers, as they cause random disk I/O and page fragmentation during insertion.
• Never modify the primary key value in business logic once the record is created.