Common SQL Optimization Techniques

1. Drive Large Tables with Small Tibles

Use a small table as the driving table to reduce the amount of data scanned from the large table. For JOIN operations, the small table should be the driving table, provided the large table has an index on the join column. For IN subqueries, MySQL executes the subquery first, so put the small table inside IN. For EXISTS subqueries, the mainn query is evaluated first, so the small table should be in the subquery.

-- IN example: A should be the small table
SELECT rate FROM B WHERE id IN (SELECT id FROM A);

-- EXISTS example: A should be the small table
SELECT rate FROM B WHERE EXISTS (SELECT 1 FROM A WHERE B.id = A.id AND money > 1000);

Note: IN can only restrict one column, while EXISTS can handle multiple columns.

2. Avoid Using SELECT *

• Retrieves unnecessary columns, wasting memory and CPU.
• Prevents covering index usage, causing extra table loookups.

3. Prefer UNION ALL over UNION

UNION ALL returns all rows including duplicates, while UNION performs deduplication (sorting and comparing), which is more resource-intensive.

4. Use Batch Inserts Instead of Single Inserts

Batching reduces the number of database round trips. If inserting more than 500 rows, split into batches.

// Example using MyBatis batch insert
orderMapper.insertBatch(list);

5. Use LIMIT 1 Instead of COUNT(*) to Check Existence

COUNT(*) scans the entire table, while LIMIT 1 stops after finding the first match.

-- Instead of:
SELECT COUNT(*) FROM user WHERE status = 1 AND sex = 1;

-- Use:
SELECT 1 FROM user WHERE status = 1 AND sex = 1 LIMIT 1;

// Java code check
Integer exist = userDao.existByStatusAndSex(params);
if (exist != null) {
    // exists
} else {
    // not exists
}

6. Filter Before GROUP BY

Apply WHERE conditions before grouping to reduce the number of rows being grouped.

7. Avoid Index Invalidation

• OR conditions: Index is used only if both columns in OR are indexed; otherwise, use UNION or IN.
• LIKE with leading wildcard: LIKE '%abc' does not use index; LIKE 'abc%' does.
• Composite index: The first column must be in the query condition.
• Similar selectivity: If using an index is not more efficient than a full scan, the index might not be used.
• Column operations or functions on indexed columns: Avoid WHERE rate + 1 = 2 or WHERE SUBSTR(rate, 1, 3) = '100'.

8. Avoid Subqueries in CASE WHEN Expressions

If a CASE WHEN expression contains a SELECT subquery, it runs for each row, hurting performance. Rewrite using a JOIN.

-- Avoid:
SELECT a.id,
       CASE WHEN a.formid IN (SELECT id FROM b WHERE b.salary > 0) THEN 1 ELSE 2 END AS test
FROM test a;

-- Better:
SELECT a.id,
       CASE WHEN c.id IS NOT NULL THEN 1 ELSE 2 END AS test
FROM test a
LEFT JOIN (SELECT id FROM b WHERE b.salary > 0) c ON c.id = a.formid;