Database indexing and query timing optimization in Python

import sqlite3
import time


def time_query(db_path, query, params=()):
    conn = sqlite3.connect(db_path)
    conn.execute("PRAGMA journal_mode = WAL")
    start = time.perf_counter()
    result = conn.execute(query, params).fetchall()
    elapsed = time.perf_counter() - start
    conn.close()
    return result, elapsed


def add_index(db_path, table, column):
    conn = sqlite3.connect(db_path)
    index_name = f"idx_{table}_{column}"
    conn.execute(f"CREATE INDEX IF NOT EXISTS {index_name} ON {table}({column})")
    conn.commit()
    conn.close()
    return index_name


if __name__ == "__main__":
    db = "scaling_optimization.db"
    conn = sqlite3.connect(db)
    conn.execute("DROP TABLE IF EXISTS users")
    conn.execute("CREATE TABLE users (id INTEGER, email TEXT, age INTEGER)")
    conn.executemany(
        "INSERT INTO users VALUES (?, ?, ?)",
        [(i, f"user{i}@example.com", 18 + (i % 50)) for i in range(10_000)],
    )
    conn.commit()
    conn.close()

    slow_query = "SELECT * FROM users WHERE email = ?"
    slow_result, slow_time = time_query(db, slow_query, ("user5000@example.com",))

    index_name = add_index(db, "users", "email")

    fast_result, fast_time = time_query(db, slow_query, ("user5000@example.com",))

    print(f"Slow query: {slow_time:.6f}s -> {slow_result}")
    print(f"Index created: {index_name}")
    print(f"Fast query: {fast_time:.6f}s -> {fast_result}")
    print(f"Speedup: {slow_time / fast_time:.2f}x")