11 Golden Rules for SQL Performance Optimization

1. Introduction

Database performance directly impacts business efficiency, and more than 70% of system bottlenecks stem from poorly written SQL. The article distills eleven practical rules to help developers and DBAs boost query execution speed.

2. Create Necessary Indexes

Indexes accelerate data retrieval by sorting indexed columns. Common types include Clustered, Non‑clustered, and Full‑text. Example of a composite index:

CREATE INDEX idx_name_age ON t_user (name, age);

This non‑clustered index speeds up lookups on name and age.

3. Avoid SELECT *

Fetching all columns wastes I/O and CPU, especially on large tables. Prefer selecting only required columns:

-- Bad
SELECT * FROM t_user;
-- Good
SELECT id, name FROM t_user;

4. Limit Rows Returned

Use LIMIT to restrict result size and prevent unnecessary scanning:

SELECT name FROM users WHERE user_group IN ('VIP','PLATINUM')
ORDER BY user_group DESC
LIMIT 10; -- returns only the top 10 rows

5. Optimize WHERE Clause

Filter early with highly selective conditions (e.g., primary‑key lookups).

Avoid functions on columns; they block index usage.

Prefer equality (=) over pattern matching (LIKE) and direct date comparisons.

Bad vs. good example:

-- Bad
SELECT id, name FROM t_user WHERE YEAR(join_date) = 2023;
-- Good
SELECT id, name FROM t_user WHERE join_date >= '2023-01-01' AND join_date < '2024-01-01';

6. Function Indexes (When Supported)

Oracle, PostgreSQL, and MySQL 8.0+ allow indexing on expressions. Example for indexing the year of a date column:

-- PostgreSQL / Oracle
CREATE INDEX idx_join_date ON t_user (EXTRACT(YEAR FROM join_date));
-- MySQL 8.0+
CREATE INDEX idx_join_date ON t_user ((YEAR(join_date)));

7. Efficient JOIN Usage

Joins combine related rows from multiple tables. Types demonstrated:

-- INNER JOIN (only matching rows)
SELECT s.student_name, c.course_name FROM students s INNER JOIN courses c ON s.course_id = c.course_id;

-- LEFT JOIN (all left rows, NULL for missing right rows)
SELECT s.student_name, c.course_name FROM students s LEFT JOIN courses c ON s.course_id = c.course_id;

-- FULL JOIN (all rows from both sides) – not supported in MySQL 5.x/8.x
SELECT s.student_name, c.course_name FROM students s FULL JOIN courses c ON s.course_id = c.course_id;

MySQL workaround using LEFT JOIN … UNION … RIGHT JOIN is provided. Tips for fast joins:

Start with the smallest table.

Index join columns.

Replace complex joins with subqueries or CTEs.

CTE example:

WITH RecentEnrollments AS (
    SELECT student_id, course_id FROM enrollments WHERE enrollment_date >= '2025-10-01'
)
SELECT s.student_name, c.course_name
FROM students s
INNER JOIN RecentEnrollments re ON s.student_id = re.student_id
INNER JOIN courses c ON re.course_id = c.course_id;

8. Analyze Execution Plans

Use EXPLAIN or EXPLAIN ANALYZE to see how the engine processes a query. Common issues revealed include full table scans, inefficient joins, and excessive sorting or temporary tables.

9. Optimize Subqueries

Prefer joins over subqueries when possible.

Use CTEs for readability and maintainability.

Choose non‑correlated subqueries to avoid per‑row execution.

Examples:

-- Non‑correlated subquery (runs once)
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE country = 'CN');

-- Correlated subquery (runs per row, O(n²))
SELECT * FROM orders o WHERE o.amount > (SELECT AVG(amount) FROM orders o2 WHERE o2.customer_id = o.customer_id);

10. Limit Use of DISTINCT

DISTINCT can be costly on large tables. Prefer GROUP BY or window functions:

-- Avoid
SELECT DISTINCT dept_name FROM employees;
-- Better
SELECT dept_name FROM employees GROUP BY dept_name;

-- Window function alternative
SELECT emp_name, dept_name FROM (
    SELECT emp_name, dept_name, ROW_NUMBER() OVER (PARTITION BY emp_name, dept_name ORDER BY emp_id) AS row_num
    FROM employees
) e WHERE row_num = 1;

11. Avoid Unnecessary ORDER BY / GROUP BY

Use only when the result truly needs sorting or aggregation.

Ensure the ordered/grouped columns are indexed.

Consider performing sorting in the application layer.

Pre‑aggregate or use materialized views for repeated heavy aggregations.

12. Use UNION ALL Instead of UNION

UNION removes duplicates, incurring extra processing. When duplicate removal is not required, UNION ALL is faster, especially on millions of rows.

-- Slower (deduplication)
SELECT student_id FROM students_2024 UNION SELECT student_id FROM students_2025;

-- Faster (keep all rows)
SELECT student_id FROM students_2024 UNION ALL SELECT student_id FROM students_2025;

13. Split Complex Queries

Break large queries into smaller parts or materialized views. Example (Oracle) creates a materialized view to cache aggregated attendance data, refreshes it on demand, and queries the view for fast results. MySQL does not support materialized views.

-- Create base table
CREATE TABLE attendance (
    student_id NUMBER,
    attendance_date DATE,
    days_present NUMBER DEFAULT 1
);

-- Insert sample data
INSERT INTO attendance VALUES (101, TO_DATE('2023-01-01','YYYY-MM-DD'), 1);
INSERT INTO attendance VALUES (101, TO_DATE('2023-01-02','YYYY-MM-DD'), 1);
INSERT INTO attendance VALUES (102, TO_DATE('2023-01-01','YYYY-MM-DD'), 1);

-- Create materialized view
CREATE MATERIALIZED VIEW monthly_attendance
REFRESH COMPLETE ON DEMAND AS
SELECT student_id, SUM(days_present) AS total_days FROM attendance GROUP BY student_id;

-- Query the view
SELECT * FROM monthly_attendance;

Refreshing the view:

BEGIN
   DBMS_MVIEW.REFRESH(
       list => 'monthly_attendance',
       method => 'C',
       parallelism => 4,
       refresh_after_errors => FALSE
   );
END;
/