Reference library

Database scaling & optimization

Indexing, connection pooling, read replicas, query tuning, and throughput-aware SQL.

5 matches

Composite index leftmost prefix in Python

Simulate a composite index in SQLite and check whether query columns match the leftmost prefix rule for index usage.

import sqlite3


def get_indexed_columns(table_name):
    """Simulate a composite index by reading column names that start with 'idx_'."""
    conn = sqlite3.connect(":memory:")
    conn.execute(f"CREATE TABLE {table_name} (id INTEGER, idx_col1 TEXT, idx_col2 INTEGER, other TEXT)")
    conn.execute(f"CREATE INDEX idx_…

14 0 Open

Database scaling & optimization easy

How to Avoid SELECT * and Mock SQL Column Queries in Python

Mock a SQLite cursor to verify that queries specify explicit columns instead of using SELECT *.

sqlite mock testing

Python

import sqlite3
from unittest.mock import Mock, patch


def get_user_emails(connection):
    """Fetch only the required columns instead of SELECT *."""
    cursor = connection.cursor()
    cursor.execute("SELECT email FROM users")
    return [row[0] for row in cursor.fetchall()]


def test_get_user_emails_specific_colu…

13 0 Open

Database scaling & optimization medium

How to Create a Covering Index with INCLUDE Columns in Python

Create a covering index with INCLUDE columns in SQLite from Python and inspect the query plan to confirm the index covers the query.

sqlite indexing covering index

Python

import sqlite3

def create_covering_index_mock():
    conn = sqlite3.connect(":memory:")
    cursor = conn.cursor()

    cursor.execute("""
        CREATE TABLE employees (
            id INTEGER PRIMARY KEY,
            name TEXT,
            department TEXT,
            salary INTEGER
        )
    """)

    employe…

15 0 Open

Database scaling & optimization easy

How to Speed Up Column Lookups with DataFrame Index in Python

Use pandas set_index to make repeated column value lookups O(1)-style fast instead of scanning the whole DataFrame each time.

pandas indexing performance

Python

import pandas as pd

# Mock dataset with duplicate customer IDs
data = {"customer_id": [101, 102, 103, 101, 104, 102],
        "order_amount": [250.0, 85.5, 300.0, 175.25, 420.0, 95.75]}

df = pd.DataFrame(data)
df = df.set_index("customer_id")

# Simulated lookup request
search_id = 102

# Fast index-based lookup (no…

15 0 Open

Database scaling & optimization easy

How to enforce a unique index constraint in Python

Mock a database unique index in Python that rejects duplicate rows based on one or more columns.

database unique index constraint

Python

class MockIndex:
    def __init__(self, columns):
        self.columns = columns
        self._values = set()

    def insert(self, row):
        key = tuple(row[col] for col in self.columns)
        if key in self._values:
            raise ValueError(f"Duplicate key {key} for columns {self.columns}")
        self._v…

14 0 Open

Browse by section

Each section groups closely related Python snippets.