SQL Indexing & Query Optimization (Part 2): Smart Queries and Speed Tricks

Indexing and Query Optimization with SQL: A Simple and Fun Tutorial for Everyone-Part 2

---

← Missed the first part? Start with Part 1 of this tutorial here.

---

Unoptimized Query:

import sqlite3
import time

conn = sqlite3.connect('toystore.db')
cursor = conn.cursor()

# Unoptimized: Select all columns, no index
start_time = time.time()
cursor.execute('''
    SELECT *
    FROM Sales
    JOIN Toys ON Sales.ToyID = Toys.ToyID
    WHERE Toys.Type = 'Action Figure' AND Sales.SaleDate = '2025-09-02'
''')
print("Unoptimized Results:", cursor.fetchall())
print("Time taken:", time.time() - start_time, "seconds")

conn.close()

Output:

Unoptimized Results: [(3, 1, 1, 30.0, '2025-09-02', 1, 'Robot', 'Action Figure', 30.0, 10)]
Time taken: 0.002 seconds

Optimized Query:

import sqlite3
import time

conn = sqlite3.connect('toystore.db')
cursor = conn.cursor()

# Create indexes
cursor.execute("CREATE INDEX IF NOT EXISTS idx_toy_type ON Toys (Type)")
cursor.execute("CREATE INDEX IF NOT EXISTS idx_sale_date ON Sales (SaleDate)")

conn.commit()

# Optimized: Select specific columns, use indexed columns
start_time = time.time()
cursor.execute('''
    SELECT Toys.Name, Sales.TotalPrice
    FROM Sales
    JOIN Toys ON Sales.ToyID = Toys.ToyID
    WHERE Toys.Type = 'Action Figure' AND Sales.SaleDate = '2025-09-02'
''')
print("Optimized Results:", cursor.fetchall())
print("Time taken:", time.time() - start_time, "seconds")

conn.close()

Output:

Optimized Results: [('Robot', 30.0)]
Time taken: 0.001 seconds

What’s Happening?

• Indexes on Type and SaleDate make filtering faster.
• SELECT Toys.Name, Sales.TotalPrice grabs only needed columns.
• The query runs faster because SQLite uses the indexes.

---

More Optimization Tips

1. Index Frequently Searched Columns

Create indexes on columns you often use in WHERE, JOIN, or ORDER BY. For example:

CREATE INDEX idx_toy_price ON Toys (Price);

2. Use EXPLAIN to Check Queries

SQLite’s EXPLAIN QUERY PLAN shows how a query runs. Try it to see if your index is used:

conn = sqlite3.connect('toystore.db')
cursor = conn.cursor()
cursor.execute("EXPLAIN QUERY PLAN SELECT * FROM Toys WHERE Name = 'Robot'")
print("Query Plan:", cursor.fetchall())
conn.close()

Sample Output:

[(3, 0, 0, 'SEARCH TABLE Toys USING INDEX idx_toy_name (Name=?)')]

Explanation: This means SQLite is using the idx_toy_name index to perform the search on the Name column. If it said SCAN TABLE, it would mean no index is being used, which is slower.

This shows if SQLite uses idx_toy_name.

3. Avoid Over-Indexing

Indexes speed up searches but slow down INSERT, UPDATE, and DELETE because the index must be updated. Only index columns you search often.

4. Combine with Joins

Use indexes on join columns (like ToyID in Sales and Toys):

CREATE INDEX idx_sale_toyid ON Sales (ToyID);

Example: Optimized Join

conn = sqlite3.connect('toystore.db')
cursor = conn.cursor()

cursor.execute("CREATE INDEX IF NOT EXISTS idx_sale_toyid ON Sales (ToyID)")
conn.commit()

cursor.execute('''
    SELECT Toys.Name, SUM(Sales.TotalPrice)
    FROM Sales
    JOIN Toys ON Sales.ToyID = Toys.ToyID
    WHERE Sales.SaleDate = '2025-09-02'
    GROUP BY Toys.Name
''')
print("Sales on 2025-09-02:", cursor.fetchall())

conn.close()

Output:

Sales on 2025-09-02: [('Doll', 25.0), ('Robot', 30.0)]

The index on ToyID makes the join faster.

---

Tips for Success

1. Start Simple: Create one index and test a query.
2. Test with EXPLAIN: Check if your index is used.
3. Balance Indexes: Don’t index every column—only the ones you search.
4. Write Clear Queries: Use specific columns and filters.
5. Practice: Try indexing and optimizing queries for a game or book database.

---

💡 Quick Recap: Why Optimization Matters

If you’ve just joined us, or need a reminder — here’s why query optimization is a must-have skill in your SQL toolbox:

• They are Fast: Indexes cut search time dramatically.
• They are Easy: Just a few commands to create indexes.
• They are Useful: Fast databases are key for apps and websites.
• They are Fun: It’s like giving your database a turbo boost!

---

🚀 Ready to Speed Up Your Queries?

With just a few well-placed indexes and optimized queries, you can dramatically improve your database’s speed and performance. Now you’ve got the tools — go make your database fly!

---

Common Questions

1. Are indexes always good?

No, they slow down writes (INSERT, UPDATE), so use them wisely.

2. Do indexes work in other databases?

Yes, MySQL, PostgreSQL, and others use similar indexing.

3. How do I know if my query is optimized?

Use EXPLAIN QUERY PLAN to check if indexes are used.

4. Can I remove an index?

Yes, with DROP INDEX index_name.

---

🧠 Mini Challenge: Test Your Optimization Skills!

Try answering these on your own before checking online:

1. Which columns in the Sales table are good candidates for indexing?
2. What does the EXPLAIN output SCAN TABLE Toys mean?
3. Why might too many indexes slow down INSERT operations?
4. How would you optimize a query that joins Toys and a new Suppliers table?

Bonus: Create your own small database (e.g., books, games, recipes) and try adding indexes and optimized queries!

---

Wrapping Up

Indexing and query optimization are like giving your database a treasure map and shortcuts to find data fast. In this tutorial, we created indexes on our toystore.db tables, optimized queries with specific columns and filters, and saw how to check query plans. These skills make your database zoom like a superhero.

Try creating your own database for movies or games, add indexes, and optimize queries. Use DB Browser for SQLite to see your data or keep coding in Python. With indexing and query optimization, you’re now a database speed wizard, ready to make your data fly!

Happy SQL adventures, and keep speeding up your database!

← Previous Next →

Advanced Query Techniques for SQL and NoSQL Databases: Aggregations, Joins & More

🔷 Part 15: Advanced Query Techniques – Master Complex Queries in SQL and NoSQL

---

📍 Introduction

Once you’re comfortable with basic queries, it’s time to learn advanced techniques that let you handle complex data retrieval and analysis efficiently.

This part covers:

• SQL joins and subqueries

• NoSQL aggregation pipelines

• Combining data from multiple collections or tables

• Performance considerations

📚 If you missed Part 14: Data Modeling Best Practices, read it here.

---

🔸 1. SQL Advanced Queries: Joins & Subqueries

Joins:

• INNER JOIN: Returns rows with matching values in both tables.

• LEFT JOIN: Returns all rows from left table, with matching rows from right table or NULL.

• RIGHT JOIN: Opposite of LEFT JOIN.

• FULL OUTER JOIN: Returns rows when there is a match in one of the tables.

---

Example: Get all books and their authors

This query retrieves book titles along with their author names using an INNER JOIN.

SELECT books.title, authors.name
FROM books
INNER JOIN authors ON books.author_id = authors.id;

---

Subqueries:

• A query nested inside another query.

• Useful for filtering, aggregating, or transforming data.

SELECT title FROM books
WHERE author_id IN (SELECT id FROM authors WHERE country = 'USA');

---

🔹 2. NoSQL Aggregation Pipelines (MongoDB)

• Aggregation pipelines process data through multiple stages.

• Common stages: $match (filter), $group (aggregate), $sort, $project (reshape).

---

Example: Count books by genre

db.books.aggregate([
  { $group: { _id: "$genre", totalBooks: { $sum: 1 } } },
  { $sort: { totalBooks: -1 } }
]);

---

🔸 3. Combining Data from Multiple Collections

• MongoDB uses $lookup for joining collections (like SQL JOIN).

Example: Books with author details

db.books.aggregate([
  {
    $lookup: {
      from: "authors",
      localField: "author_id",
      foreignField: "_id",
      as: "authorDetails"
    }
  }
]);

---

🔹 4. Performance Tips

• Use indexes on join/filter fields.

• Avoid unnecessary fields in SELECT or $project.

• Limit result set sizes.

• Analyze query plans (EXPLAIN in SQL, .explain() in MongoDB).

---

📝 Summary

Feature	SQL	NoSQL (MongoDB)
Joins	INNER, LEFT, RIGHT, FULL OUTER	$lookup in aggregation pipeline
Aggregations	GROUP BY, HAVING	$group, $match, $sort
Subqueries	Nested SELECT	Sub-pipelines or $facet
Performance Optimization	Indexes, query plans	Indexes, .explain(), pipelines

---

❓ Frequently Asked Questions

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only matching rows from both tables, while LEFT JOIN returns all rows from the left table and matching rows from the right (or NULL).

Can MongoDB use JOINs like SQL?

MongoDB doesn’t use traditional JOINs, but the $lookup stage in aggregation can simulate joins between collections.

---

✅ Next Steps

In Part 16, we will explore Database Scaling Techniques — vertical and horizontal scaling approaches for growing databases.

← Previous Next →