Skip to content
30 MySQL SQL Query Optimization Techniques: Practical Guide with Examples
Databases 16 min read

30 MySQL SQL Query Optimization Techniques: Practical Guide with Examples

This comprehensive guide covers 30 practical MySQL SQL query optimization techniques, including indexing strategies, join optimization, subquery rewrites, pagination, sorting, wildcard handling, and table design best practices, each illustrated with concrete SQL examples to improve database performance and query efficiency.

Linyb Geek Road
Linyb Geek Road
Linyb Geek Road

Introduction

Optimizing SQL queries is critical for improving MySQL database performance and response speed. Proper query optimization reduces database load, enhances query efficiency, and delivers better user experience. This article presents 30 commonly used MySQL SQL query optimization methods with practical examples demonstrating their application.

Part 1: Basic Optimization Methods

1. Use Indexes

Indexes are fundamental to improving database query performance. Adding appropriate indexes on queried columns accelerates query speed. When creating indexes, consider query frequency and data update frequency to avoid over-indexing or unnecessary indexes.

-- Create index
CREATE INDEX idx_column ON table_name (column_name);

2. Avoid SELECT *

When querying data, avoid SELECT * and explicitly specify required columns. This reduces returned data volume and improves query efficiency.

-- Not recommended
SELECT * FROM table_name;

-- Recommended
SELECT column1, column2 FROM table_name;

3. Use EXPLAIN to Analyze Query Plans

The EXPLAIN command analyzes query execution plans, helping optimize SQL statements and identify potential performance issues.

EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';

Part 2: Optimizing Query Conditions

4. Use WHERE Clause to Filter Data

Use WHERE clauses to filter data during queries, reducing returned data volume.

-- Not recommended
SELECT * FROM table_name;

-- Recommended
SELECT * FROM table_name WHERE column_name = 'value';

5. Use Covering Index Queries

A covering index query occurs when all queried columns are included in the index, eliminating the need for table lookups. This reduces I/O operations and improves query efficiency.

-- Create index
CREATE INDEX idx_column ON table_name (column_name);

-- Covering index query
SELECT column1, column2 FROM table_name WHERE column_name = 'value';

6. Avoid Functions in WHERE Clauses

Using functions in WHERE clauses causes index invalidation, forcing full table scans. Avoid functions in WHERE clauses.

-- Not recommended
SELECT * FROM table_name WHERE DATE_FORMAT(date_column, '%Y-%m-%d') = '2023-01-01';

-- Recommended
SELECT * FROM table_name WHERE date_column = '2023-01-01';

7. Use Appropriate Data Types

Choosing suitable data types reduces storage space and query time, improving database performance.

-- Not recommended
CREATE TABLE table_name (id VARCHAR(100), name VARCHAR(100));

-- Recommended
CREATE TABLE table_name (id INT, name VARCHAR(100));

Part 3: Join Query Optimization

8. Use INNER JOIN Instead of WHERE Clause Joins

INNER JOIN better expresses table relationships, improving query readability and performance.

-- Not recommended
SELECT * FROM table1, table2 WHERE table1.id = table2.id;

-- Recommended
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

9. Use JOIN ON Instead of WHERE for Join Filtering

In join queries, use JOIN ON for join filtering rather than filtering joins in WHERE clauses.

-- Not recommended
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id WHERE table2.name = 'value';

-- Recommended
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id AND table2.name = 'value';

10. Use Appropriate Join Types

Select suitable join types based on business requirements: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN.

-- INNER JOIN (default join type)
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

-- LEFT JOIN
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;

-- RIGHT JOIN
SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id;

-- FULL JOIN
SELECT * FROM table1 FULL JOIN table2 ON table1.id = table2.id;

Part 4: Subquery Optimization

11. Use EXISTS Instead of IN

When using subqueries, prefer EXISTS over IN. EXISTS only checks for record existence, while IN loads the subquery result set into memory, potentially causing performance issues.

-- Not recommended
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);

-- Recommended
SELECT * FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.id = table2.id);

12. Use JOIN Instead of Subqueries

Using JOIN in queries better expresses query logic and avoids complex subqueries.

-- Not recommended
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);

-- Recommended
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

Part 5: LIMIT Optimization

13. Use LIMIT to Restrict Returned Records

When querying large datasets, LIMIT restricts returned record count, avoiding excessive data retrieval.

-- Return first 10 records
SELECT * FROM table_name LIMIT 10;

14. Use Pagination Queries

For paginated data queries, combine LIMIT with OFFSET to implement pagination.

-- Return page 1 data, 10 records per page
SELECT * FROM table_name LIMIT 0, 10;

-- Return page 2 data, 10 records per page
SELECT * FROM table_name LIMIT 10, 10;

Part 6: Sorting Optimization

15. Use Appropriate Sort Fields

Selecting appropriate sort fields reduces sorting time and overhead. Typically choose indexed fields for sorting, avoiding sorting large data volumes.

-- Not recommended
SELECT * FROM table_name ORDER BY name;

-- Recommended
SELECT * FROM table_name ORDER BY indexed_column;

16. Use Covering Indexes to Reduce Sorting

If a query only needs sort fields and those fields are indexed, covering indexes can reduce sorting time.

-- Create index
CREATE INDEX idx_name ON table_name (name);

-- Use covering index
SELECT name FROM table_name ORDER BY name;

17. Use DESC for Descending Sort

When performing descending sorts, using DESC explicitly specifies sort order, avoiding unnecessary sorting operations.

-- Descending sort
SELECT * FROM table_name ORDER BY column_name DESC;

Part 7: Avoid Wildcards

18. Avoid Leading % Wildcards

Avoid using % wildcards at the beginning of query fields, as this causes index invalidation and forces full table scans.

-- Not recommended
SELECT * FROM table_name WHERE column_name LIKE '%value';

-- Recommended
SELECT * FROM table_name WHERE column_name LIKE 'value%';

19. Use Prefix Indexes

If queries require trailing wildcards, prefix indexes can optimize query performance.

-- Create prefix index
CREATE INDEX idx_column ON table_name (column_name(10));

-- Use prefix index
SELECT * FROM table_name WHERE column_name LIKE 'value%';

Part 8: Union Query Optimization

20. Use UNION ALL Instead of UNION

When deduplication is not needed in union queries, use UNION ALL to reduce query overhead.

-- Not recommended
SELECT * FROM table1 WHERE condition
UNION
SELECT * FROM table2 WHERE condition;

-- Recommended
SELECT * FROM table1 WHERE condition
UNION ALL
SELECT * FROM table2 WHERE condition;

21. Use EXISTS Instead of IN and UNION

Using EXISTS instead of IN and UNION better expresses query logic and improves performance.

-- Not recommended
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2 WHERE condition)
UNION
SELECT * FROM table1 WHERE id IN (SELECT id FROM table3 WHERE condition);

-- Recommended
SELECT * FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.id = table2.id AND condition)
OR EXISTS (SELECT 1 FROM table3 WHERE table1.id = table3.id AND condition);

Part 9: Subquery Optimization (Continued)

22. Use Inner Joins Instead of Subqueries

When using subqueries, prefer inner joins to reduce query overhead.

-- Not recommended
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);

-- Recommended
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

23. Use EXISTS Instead of IN

When using subqueries, prefer EXISTS over IN. EXISTS only checks for record existence, while IN loads the subquery result set into memory, potentially causing performance issues.

-- Not recommended
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);

-- Recommended
SELECT * FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.id = table2.id);

Part 10: Table Design Optimization

24. Use Appropriate Data Types

When creating tables, choosing suitable data types reduces storage space and query time, improving database performance.

-- Not recommended
CREATE TABLE table_name (id VARCHAR(100), name VARCHAR(100));

-- Recommended
CREATE TABLE table_name (id INT, name VARCHAR(100));

25. Vertical Table Partitioning

When tables contain large amounts of redundant data, consider vertical partitioning to split different data into separate tables, improving query performance.

Part 11: Other Optimization Methods

26. Use Connection Pools

Connection pools reduce database connection overhead and improve concurrent performance.

27. Configure Buffers Appropriately

Based on actual database conditions, configure buffer sizes appropriately to accelerate data read/write speeds.

28. Use Delayed Joins

During join queries, consider delayed joins — executing join operations last to reduce join frequency.

29. Avoid Temporary Tables

Avoid temporary tables in queries as they increase query overhead.

30. Regularly Optimize Tables

Regular table optimization, including index rebuilding and table compression, improves database performance.

Conclusion

This article introduced 30 commonly used MySQL SQL query optimization methods with practical examples. SQL query optimization is key to enhancing database performance and response speed. Readers should select appropriate optimization methods based on their specific scenarios and business requirements to improve database performance and availability. Continuous learning and practice in database technologies are essential to becoming an excellent database engineer.

Original Source

Signed-in readers can open the original source through BestHub's protected redirect.

Sign in to view source
Republication Notice

This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactadmin@besthub.devand we will review it promptly.

indexingMySQLpaginationdatabase designquery performanceSQL optimizationjoinssubqueries
Reader feedback

How this landed with the community

Sign in to like

Rate this article

Was this worth your time?

Sign in to rate
Discussion

0 Comments

Thoughtful readers leave field notes, pushback, and hard-won operational detail here.