Query Optimization

What is Query Optimization?

Making your SQL queries run faster and use less resources.

Simple rule: Do less work = Faster results

How Optimization Works

6 Quick Optimization Tips

1. Select Only Needed Columns

• Bad: SELECT * (gets everything)
• Good: SELECT name, age (gets only what you need)

2. Filter Early with WHERE

• Bad: Get all rows, filter in app
• Good: WHERE status = 'active' (filter in database)

3. Use LIMIT for Large Results

• Bad: Return 100,000 rows
• Good: LIMIT 100 (return only what you display)

4. Create Indexes on Search Columns

• Bad: Full table scan
• Good: Index on columns used in WHERE/JOIN/ORDER BY

5. Avoid Unnecessary DISTINCT

• Bad: SELECT DISTINCT when not needed
• Good: Only use DISTINCT when duplicates exist

6. JOIN vs Subquery

• Subquery: Can be slower (runs multiple times)
• JOIN: Often faster (single pass)

Optimization Checklist

• ✅ Only select columns you need
• ✅ Add WHERE clause to filter
• ✅ Use LIMIT for pagination
• ✅ Index frequently searched columns
• ✅ Avoid SELECT * in production
• ✅ Use EXPLAIN to analyze queries

Common Mistakes

• **SELECT *** - fetches unnecessary data
• No indexes - causes full table scans
• No LIMIT - returns too many rows
• Filter in app - should filter in SQL

Summary

• Less data = Faster queries
• Use WHERE, LIMIT, specific columns
• Add indexes on searched columns
• Analyze with EXPLAIN