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.

Medium Python 3.9+ Aug 9, 2026 Database scaling & optimization 15 views 0 copies

sqlite indexing covering index query plan performance

Python code

54 lines

Python 3.9+

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
        )
    """)

    employees = [
        (1, "Alice", "Engineering", 90000),
        (2, "Bob", "Sales", 75000),
        (3, "Charlie", "Engineering", 85000),
        (4, "Diana", "Marketing", 70000),
    ]
    cursor.executemany("INSERT INTO employees VALUES (?, ?, ?, ?)", employees)

    cursor.execute("""
        CREATE INDEX idx_emp_dept_covering
        ON employees(department)
        INCLUDE (name, salary)
    """)

    result = cursor.execute("""
        SELECT department, name, salary
        FROM employees
        WHERE department = 'Engineering'
        ORDER BY name
    """).fetchall()

    explain = cursor.execute("EXPLAIN QUERY PLAN " + """
        SELECT department, name, salary
        FROM employees
        WHERE department = 'Engineering'
        ORDER BY name
    """).fetchall()

    conn.close()
    return result, explain

if __name__ == "__main__":
    rows, plan = create_covering_index_mock()
    print("Query results:")
    for row in rows:
        print(row)
    print("\nQuery plan:")
    for step in plan:
        print(step)

Output

stdout

Query results:
('Engineering', 'Alice', 90000)
('Engineering', 'Charlie', 85000)

Query plan:
(4, 0, 0, 'SEARCH employees USING INDEX idx_emp_dept_covering (department=?)')

How it works

The CREATE INDEX ... INCLUDE syntax considers name and salary in the index leaf nodes so the database never has to visit the table for this query. By placing department in the leading column and ordering by name, the index makes the lookup and sort efficient. The query planner's output shows a SEARCH step using the covering index, proving the table is not accessed. This pattern works in SQLite and PostgreSQL (with slightly different syntax like INCLUDE in Postgres). The in-memory SQLite database lets you prototype and validate query execution plans without a server.

Common mistakes

Assuming INCLUDE columns order matters — they don't; only the key columns affect search/ordering.
Forgetting to check the EXPLAIN query plan to verify the index actually covers the query.
Using SELECT * with a covering index defeats the purpose if columns aren't included.
Using INCLUDE on a database like MySQL where it's not supported.

Variations

Use `CREATE INDEX ... INCLUDE` in PostgreSQL with the same idea.
For databases without INCLUDE, create a composite index on all needed columns in the right order.

Real-world use cases

Optimizing an e-commerce product listing query that filters by category and returns price and name without hitting the table.
Speeding up a reporting job that queries a user activity table for selected columns by date range.
Reducing I/O in an analytics dashboard that joins a fact table filtered by a dimension field.

How to Create a Covering Index with INCLUDE Columns in Python

Python code

Output

How it works

Common mistakes

Variations

Real-world use cases

More from Database scaling & optimization

Tutorials

Quizzes

Python code

Output

How it works

Common mistakes

Variations

Real-world use cases

More from Database scaling & optimization

Keep learning

Tutorials

Quizzes