Top 20 SQL Optimization Techniques for Better Query Performance

When writing and optimizing SQL statements, several general tips and best practices can help improve query performance. Below are 20 SQL optimization examples with explanations.

1. Use Indexes

CREATE INDEX idx_users_email ON users(email);

Create indexes on columns that are frequently used in queries.

2. Select Only Necessary Columns

SELECT id, name, email FROM users WHERE status = 'active';

Avoid using SELECT *; select only the columns you need.

3. Avoid Function Calls on Columns

SELECT * FROM users WHERE created_at >= '2023-01-01';

Do not apply functions to columns in the WHERE clause (e.g., DATE(created_at)) because this can invalidate indexes.

4. Use Joins Instead of Subqueries

SELECT orders.id, orders.total, users.name
FROM orders
JOIN users ON orders.user_id = users.id
WHERE orders.total > 100;

Use JOIN queries instead of subqueries to improve performance.

5. Avoid Redundant Full Table Scans

SELECT id, name FROM users WHERE email = '[email protected]' LIMIT 1;

Use LIMIT to restrict the number of rows returned and avoid full table scans.

6. Use EXISTS Instead of IN

SELECT name FROM users WHERE EXISTS (
    SELECT 1 FROM orders WHERE orders.user_id = users.id AND orders.total > 100
);

Replace IN with EXISTS for better performance when checking subquery existence.

7. Batch Updates or Inserts

INSERT INTO orders (user_id, product_id, quantity) VALUES
(1, 1, 2),
(2, 3, 1),
(3, 2, 4);

Use batch inserts or updates to reduce the number of database interactions.

8. Use Covering Indexes

CREATE INDEX idx_users_status ON users(status, id, name);
SELECT id, name FROM users WHERE status = 'active';

Create a covering index that includes all columns used in the query.

9. Avoid Selecting Unnecessary Large Data Sets

SELECT id, name FROM users WHERE created_at BETWEEN '2023-01-01' AND '2023-01-31';

Use appropriate conditions to limit the size of the data set returned.

10. Optimize Sorting

CREATE INDEX idx_users_created_at ON users(created_at);
SELECT id, name FROM users WHERE status = 'active' ORDER BY created_at DESC;

Create an index on the sorting column to improve the performance of the ORDER BY clause.

11. Use Explicit Join Conditions

SELECT orders.id, users.name
FROM orders
JOIN users ON orders.user_id = users.id
WHERE users.status = 'active';

Specify clear join conditions in JOIN statements to avoid Cartesian products.

12. Avoid Repeated Calculations with Aggregation Functions

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

Combine GROUP BY with aggregation functions (e.g., AVG, SUM, COUNT) to avoid repeated calculations in application code.

13. Use Appropriate Data Types

ALTER TABLE users MODIFY age SMALLINT;

Choose suitable data types to reduce storage space and memory usage, improving query performance.

14. Optimize Pagination Queries

SELECT id, name FROM users ORDER BY created_at LIMIT 10 OFFSET 1000;

Use LIMIT and OFFSET to minimize the amount of data processed during pagination.

15. Avoid Unnecessary Sorting

SELECT id, name FROM users WHERE status = 'active' ORDER BY created_at;

Ensure sorting is used only when needed and create indexes on sorting columns.

16. Use Prepared Statements

PREPARE stmt FROM 'SELECT id, name FROM users WHERE email = ?';
SET @email = '[email protected]';
EXECUTE stmt USING @email;

Prepared statements improve execution efficiency and enhance security.

17. Use Transactions Wisely

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
COMMIT;

Use transactions to ensure data consistency while minimizing lock holding time.

18. Use EXPLAIN for Query Analysis

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

Run EXPLAIN to view the query execution plan and identify optimization opportunities.

19. Avoid Complex Regular Expressions

SELECT * FROM users WHERE email LIKE '%@example.com';

Prefer simple string matching over complex regular expressions in queries.

20. Choose the Right Storage Engine

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    total DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

Select an appropriate storage engine, such as InnoDB for transaction support and foreign keys, or MyISAM for read‑heavy scenarios.

By applying these optimization techniques, you can significantly improve SQL query and overall database performance. Always test and adjust based on your specific business scenarios and database configuration to achieve the best results.