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
- Start Simple: Create one index and test a query.
- Test with EXPLAIN: Check if your index is used.
- Balance Indexes: Don’t index every column—only the ones you search.
- Write Clear Queries: Use specific columns and filters.
- 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:
- Which columns in the
Sales table are good candidates for indexing?
- What does the EXPLAIN output
SCAN TABLE Toys mean?
- Why might too many indexes slow down INSERT operations?
- 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!
