Step 13
6 min read

Query Optimization

Learn how to make your queries run faster - simple tips!

What is Query Optimization?

Query Optimization Quick Guide
ProblemSolutionSpeed Improvement
SELECT *SELECT name, age2x faster
No WHEREAdd WHERE filter10x faster
No IndexCREATE INDEX100x faster
Get all rowsAdd LIMIT5x faster
4 rows

Making your SQL queries run faster.

Simple analogy: Like taking a shortcut instead of the long route.

Simple Tips to Speed Up Queries

Query Optimization Quick Guide
ProblemSolutionSpeed Improvement
SELECT *SELECT name, age2x faster
No WHEREAdd WHERE filter10x faster
No IndexCREATE INDEX100x faster
Get all rowsAdd LIMIT5x faster
4 rows

Tip 1: Select Only Needed Columns

Slow:

SELECT * FROM students; -- Gets all 20 columns (even if you need only 2)

Fast:

SELECT name, age FROM students; -- Gets only 2 columns you need

Tip 2: Use WHERE to Filter Early

Slow:

SELECT * FROM orders; -- Gets all 1 million orders, then you filter in your app

Fast:

SELECT * FROM orders WHERE status = 'completed'; -- Gets only completed orders

Tip 3: Use LIMIT for Large Results

Slow:

SELECT * FROM products; -- Gets all 100,000 products

Fast:

SELECT * FROM products LIMIT 100; -- Gets only first 100

Tip 4: Create Indexes on Searched Columns

Slow:

SELECT * FROM users WHERE email = 'john@email.com'; -- No index, searches all rows

Fast:

CREATE INDEX idx_email ON users(email); SELECT * FROM users WHERE email = 'john@email.com'; -- Index makes it instant

Tip 5: Avoid SELECT DISTINCT When Possible

Slow:

SELECT DISTINCT name FROM students; -- Checks for duplicates (extra work)

Fast (if possible):

SELECT name FROM students; -- No duplicate checking

Tip 6: Use JOIN Instead of Subqueries (Sometimes)

Slower:

SELECT * FROM students WHERE id IN (SELECT student_id FROM enrollments);

Faster:

SELECT DISTINCT students.* FROM students JOIN enrollments ON students.id = enrollments.student_id;

Real Examples

Query Optimization Quick Guide
ProblemSolutionSpeed Improvement
SELECT *SELECT name, age2x faster
No WHEREAdd WHERE filter10x faster
No IndexCREATE INDEX100x faster
Get all rowsAdd LIMIT5x faster
4 rows

Example 1: Optimized Product Search

Before:

SELECT * FROM products;

After:

CREATE INDEX idx_category ON products(category); SELECT product_name, price FROM products WHERE category = 'Electronics' AND stock > 0 LIMIT 20;

Changes:

  • Added index on category
  • Selected only needed columns
  • Added WHERE filter
  • Added LIMIT

Example 2: Optimized Order Query

Before:

SELECT * FROM orders ORDER BY order_date;

After:

CREATE INDEX idx_order_date ON orders(order_date); SELECT order_id, customer_name, total FROM orders WHERE order_date >= '2024-01-01' ORDER BY order_date DESC LIMIT 100;

Summary

Query Optimization Quick Guide
ProblemSolutionSpeed Improvement
SELECT *SELECT name, age2x faster
No WHEREAdd WHERE filter10x faster
No IndexCREATE INDEX100x faster
Get all rowsAdd LIMIT5x faster
4 rows

Quick tips:

  1. SELECT only columns you need (not *)
  2. Use WHERE to filter
  3. Add LIMIT for large results
  4. Create indexes on search columns
  5. Avoid unnecessary DISTINCT
  6. Sometimes JOIN is faster than subquery

Remember: Small improvements make big difference!

Finished this topic?

Mark it complete to track your progress and maintain your streak!

Previous
Managing Indexes
Next
Transactions
SkillsetMaster - AI, Web Development & Data Analytics Courses