Optimize Python SQL Queries with Indexing – A Practical Guide

If you've ever run a Python script that interacts with a database and felt like you were watching paint dry, you're not alone. Slow queries are one of the most frustrating bottlenecks in data-driven applications. But the fix often isn't complex—it's about making the database work smarter, not harder. And that's where indexing comes in.

Why Your Queries Are Slow (And It's Not Always Python's Fault)

Let me paint you a scenario. You're working on a PythonSkillset internal tool that retrieves user activity logs. Your query works fine with a few hundred records, but as your dataset grows to hundreds of thousands, the same query crawls. You start blaming your ORM, or maybe you think Python's just not fast enough for this.

But here's the truth: databases are designed to handle millions of records, but only if you give them the right tools. Without indexes, your database performs a "full table scan"—reading every single row to find what you need. Imagine searching for a name in a phone book that's not alphabetized. That's your unindexed database.

What Indexing Actually Does

An index is like a lookup table for your database. It stores the values from a specific column (or multiple columns) in a sorted order, along with pointers to the actual rows. When you run a query with a WHERE clause on an indexed column, the database doesn't scan the whole table. It jumps directly to the right spot.

Think of it like having an index in a textbook. Without one, you flip through every page to find a topic. With one, you go straight to the page number.

Choosing the Right Columns to Index

Not every column needs an index. In fact, over-indexing can slow down inserts and updates. Here's what I've found works best after years of tuning PythonSkillset's own database queries:

Columns You Should Index

• Primary keys (already indexed by default in most databases)
• Foreign keys used in JOINs (this is the most common performance killer)
• Columns in WHERE clauses that filter out most rows (high selectivity)
• Columns in ORDER BY or GROUP BY clauses
• Columns used in range queries (dates, price ranges, etc.)

Columns to Avoid Indexing

• Columns with very few unique values (like a boolean is_active flag)
• Columns that change frequently (each update rebuilds the index)
• Columns rarely used in queries or joins

Real-World Example: Fixing a Slow Query

Imagine you have a table called transactions with 500,000 rows. Your Python code runs this query daily:

import psycopg2

conn = psycopg2.connect("dbname=python_skillset user=admin")
cur = conn.cursor()
cur.execute("""
    SELECT user_id, amount, transaction_date
    FROM transactions
    WHERE status = 'pending'
    ORDER BY transaction_date
""")

Without an index on status and transaction_date, this query scans half a million rows every time. Here's how you fix it:

CREATE INDEX idx_transactions_status_date 
ON transactions (status, transaction_date);

This creates a composite index on both columns. Now the database jumps straight to rows with status = 'pending', and the ordering is already sorted.

After adding this index, the PythonSkillset team saw query times drop from 12 seconds to under 100 milliseconds. That's not an exaggeration.

How to Check Which Indexes Already Exist

Before you start creating indexes, check what's already there. Here's a quick way in PostgreSQL:

cur.execute("""
    SELECT indexname, indexdef 
    FROM pg_indexes 
    WHERE tablename = 'transactions'
""")
for row in cur.fetchall():
    print(row)

For MySQL, use:

SHOW INDEX FROM transactions;

The One Mistake That Wrecks Performance

Many developers create indexes on individual columns without thinking about query patterns. For example, if your query uses two columns in a WHERE clause, a composite index on both columns is usually better than separate indexes on each.

Here's why: If you have separate indexes on status and transaction_date, the database still has to merge results from both indexes. With a composite index, it's one quick lookup.

Testing Your Indexes in Python

Don't guess whether an index helps. Measure it. Here's a simple benchmark you can run:

import time

def measure_query(connection, query):
    start = time.perf_counter()
    connection.cursor().execute(query)
    end = time.perf_counter()
    return end - start

before = measure_query(conn, "SELECT * FROM transactions WHERE status = 'pending'")
# Create index here
after = measure_query(conn, "SELECT * FROM transactions WHERE status = 'pending'")

print(f"Before index: {before:.3f}s")
print(f"After index: {after:.3f}s")

One More Thing: Monitor and Clean Up

Indexes aren't "set and forget." Over time, as you insert, update, and delete rows, indexes can become fragmented or outdated. Most databases have maintenance commands:

• PostgreSQL: REINDEX
• MySQL: OPTIMIZE TABLE
• SQLite: REINDEX

Run these during low-traffic periods to keep your queries fast.

Wrapping Up

Indexing isn't the most glamorous topic, but it's one of the most effective ways to speed up Python database applications. The next time a query feels sluggish, don't immediately blame your code or your database driver. Look at the query plan, check for missing indexes, and add them strategically.

PythonSkillset's own production systems run on well-indexed databases, and the difference is night and day. Give your queries the indexes they deserve, and you'll spend less time waiting and more time building.