7.7 Python Database Performance, Indexing & Query Optimization

7.7 Python Database Performance, Indexing & Query Optimization

A Python application can have correct SQL and still perform poorly when the database contains thousands or millions of records. Database performance depends on query design, indexes, joins, filtering, result size, network traffic, connection management, and application-side processing.

For ERP systems, school-management applications, examination portals, dashboards, APIs, and reporting systems, understanding database optimization is essential for maintaining acceptable response times as data grows.

1. Learning Objectives

By the end of this lesson, you will be able to:

• Understand database performance fundamentals.
• Explain database indexes.
• Create and inspect indexes.
• Identify columns that may benefit from indexing.
• Use EXPLAIN to inspect query plans.
• Optimize WHERE conditions.
• Optimize JOIN operations.
• Avoid unnecessary data retrieval.
• Implement pagination.
• Process large datasets efficiently.
• Use batch operations from Python.
• Understand connection overhead.
• Measure query performance.
• Recognize common optimization mistakes.

2. What Makes a Database Query Slow?

A query can become slow for many reasons.

3. Database Performance Flow

Python Application
        |
        v
Database Connection
        |
        v
SQL Query
        |
        v
Query Optimizer
        |
        v
Indexes / Tables
        |
        v
Result Set
        |
        v
Network Transfer
        |
        v
Python / pandas
        |
        v
Application Output

Optimization can potentially improve several stages of this pipeline.

4. What Is an Index?

A database index is a data structure that helps the database locate rows more efficiently for suitable queries.

Conceptually, it works somewhat like an index in a textbook.

Without Index

Database
   ↓
Check many rows
   ↓
Find matching record


With Suitable Index

Index
   ↓
Locate candidate rows
   ↓
Read required records

5. Creating an Index

CREATE INDEX idx_students_class
ON students(class_id);

This creates an index on the class_id column.

6. Why Index Foreign Keys?

Foreign-key columns are frequently used in joins and filtering. Appropriate indexing can therefore be valuable.

SELECT
    s.name,
    c.class_name

FROM students AS s

INNER JOIN classes AS c
    ON s.class_id = c.id;

An index on students.class_id may help queries that frequently use this relationship.

7. Index for Search Columns

CREATE INDEX idx_students_email
ON students(email);

This can be useful when the application frequently searches for students by email.

SELECT
    id,
    name,
    email

FROM students

WHERE email = %s;

8. Unique Index

A unique index both supports lookup and enforces uniqueness.

CREATE UNIQUE INDEX idx_users_email
ON users(email);

If email uniqueness is part of the data model, a UNIQUE constraint is often the clearer schema-level expression of that rule.

9. Composite Index

A composite index contains multiple columns.

CREATE INDEX idx_marks_student_exam
ON marks(student_id, exam_id);

Composite indexes can be useful when queries commonly filter or join using the indexed column combination.

10. Column Order in Composite Indexes

Column order matters in a composite index.

CREATE INDEX idx_marks_student_subject
ON marks(student_id, subject_id);

This index is especially relevant to queries whose conditions begin with student_id. It should not automatically be assumed to be equally useful for queries filtering only by subject_id.

11. Index Selectivity

An index is generally more useful when its values allow the database to narrow the search substantially.

For example, an index on a column containing thousands of distinct email addresses may be more selective than an index on a column containing only two possible values.

12. Too Many Indexes Can Hurt Performance

Indexes are not free. They consume storage and must generally be maintained when rows are inserted, updated, or deleted.

More Indexes
     ↓
Faster Some Reads
     +
More Storage
     +
More Write Maintenance

Therefore, indexes should be based on actual workload requirements.

13. Inspect Existing Indexes

SHOW INDEX
FROM students;

This can help identify existing indexes before creating additional ones.

14. Removing an Unnecessary Index

DROP INDEX idx_students_class
ON students;

Do not remove an index merely because it appears unused in one query. Consider the complete application workload before changing production indexes.

15. What Is EXPLAIN?

EXPLAIN provides information about how MySQL plans to execute a query.

EXPLAIN
SELECT
    id,
    name

FROM students

WHERE class_id = 12;

16. Reading EXPLAIN

Depending on the query and MySQL version, the execution plan can expose information such as:

• Which table is being accessed.
• Which access method is being considered.
• Which indexes are possible.
• Which index is selected.
• Estimated rows examined.
• Additional execution information.

17. EXPLAIN from Python

query = """
EXPLAIN

SELECT
    id,
    name

FROM students

WHERE class_id = %s
"""


cursor.execute(
    query,
    (12,)
)


for row in cursor.fetchall():

    print(row)

18. EXPLAIN ANALYZE

Where supported by the MySQL version and statement type, EXPLAIN ANALYZE can provide execution information based on actually running the query.

EXPLAIN ANALYZE
SELECT
    id,
    name

FROM students

WHERE class_id = 12;

19. Avoid SELECT *

Avoid retrieving every column when the application needs only a few.

# Less precise

SELECT *
FROM students;


# Better

SELECT
    id,
    name,
    class_id

FROM students;

Selecting only required columns can reduce data transfer and application memory usage.

20. Filter Data at the Database

Do not unnecessarily retrieve a large dataset into Python and then filter it.

# Avoid for large datasets

SELECT *
FROM students;

Prefer:

SELECT
    id,
    name

FROM students

WHERE class_id = %s;

21. Python Parameterized Query

class_id = 12

cursor.execute(
    """
    SELECT
        id,
        name

    FROM students

    WHERE class_id = %s
    """,
    (
        class_id,
    )
)

rows = cursor.fetchall()

Parameterization provides safer value handling and avoids building SQL through string concatenation.

22. Avoid Functions on Search Columns When Possible

Applying a function to a column in a WHERE condition can sometimes prevent efficient use of an index.

# Potentially less index-friendly

SELECT *
FROM students
WHERE LOWER(email) = %s;

The exact optimization depends on the database version, collation, index design, and query requirements.

23. Date Filtering

Date and time filtering should be designed carefully.

SELECT
    id,
    order_date,
    total_amount

FROM orders

WHERE order_date >= %s
AND order_date < %s;

Using a half-open interval such as [start, end) can be useful for precise date-range queries, particularly when the column contains timestamps.

24. Efficient JOIN Conditions

SELECT
    s.name,
    c.class_name

FROM students AS s

INNER JOIN classes AS c
    ON s.class_id = c.id

WHERE c.id = %s;

Join columns should be selected according to the relational model, and frequently used join/filter columns should be evaluated for appropriate indexing.

25. Avoid Accidental Cartesian Products

Missing or incorrect join conditions can produce extremely large result sets.

# Dangerous pattern

SELECT *
FROM students, classes;

If every student is combined with every class, the number of generated combinations can become very large.

Use an explicit relationship:

SELECT
    s.name,
    c.class_name

FROM students AS s

INNER JOIN classes AS c
    ON s.class_id = c.id;

26. LIMIT

LIMIT restricts the number of rows returned.

SELECT
    id,
    name

FROM students

ORDER BY id

LIMIT 50;

It is useful for previews and simple pagination.

27. OFFSET Pagination

SELECT
    id,
    name

FROM students

ORDER BY id

LIMIT 50
OFFSET 100;

This retrieves a page beginning after the first 100 rows.

OFFSET pagination is easy to understand but can become less efficient for very deep pages because the database may need to process increasingly large portions of the ordered result.

28. Pagination in Python

page = 3
page_size = 50

offset = (
    page - 1
) * page_size


cursor.execute(
    """
    SELECT
        id,
        name

    FROM students

    ORDER BY id

    LIMIT %s OFFSET %s
    """,
    (
        page_size,
        offset
    )
)


rows = cursor.fetchall()

29. Keyset Pagination

For large datasets, keyset pagination can often be more efficient than deep OFFSET pagination.

SELECT
    id,
    name

FROM students

WHERE id > %s

ORDER BY id

LIMIT 50;

The application supplies the last ID received from the previous page.

30. Keyset Pagination in Python

last_id = 0
page_size = 50


cursor.execute(
    """
    SELECT
        id,
        name

    FROM students

    WHERE id > %s

    ORDER BY id

    LIMIT %s
    """,
    (
        last_id,
        page_size
    )
)


rows = cursor.fetchall()


if rows:

    last_id = rows[-1][0]

31. Fetching Large Datasets

Calling fetchall() on a very large result can consume substantial application memory.

rows = cursor.fetchall()

For large datasets, consider chunking, pagination, streaming approaches supported by the connector, or server-side aggregation.

32. Fetching in Batches

cursor.execute(
    """
    SELECT
        id,
        name

    FROM students

    ORDER BY id
    """
)


while True:

    rows = cursor.fetchmany(
        500
    )

    if not rows:

        break

    for row in rows:

        process(row)

Batch processing helps avoid loading the entire result into memory at once.

33. pandas Chunk Processing

query = """
SELECT
    id,
    name,
    marks

FROM marks
"""


for chunk in pd.read_sql(
    query,
    connection,
    chunksize=5000
):

    print(
        chunk.shape
    )

    process_chunk(chunk)

This approach is particularly useful for data-analysis pipelines involving large tables.

34. Batch INSERT

Inserting records one at a time can create unnecessary database round trips.

records = [
    ("Aarav", 12),
    ("Diya", 12),
    ("Rahul", 11),
]


cursor.executemany(
    """
    INSERT INTO students
    (name, class_id)

    VALUES (%s, %s)
    """,
    records
)


connection.commit()

35. Why Batch Operations Help

Individual Inserts

Python → DB
Python → DB
Python → DB
Python → DB


Batch Insert

Python
   ↓
Database
   ↓
Multiple Rows

Reducing round trips can significantly improve throughput for suitable workloads.

36. Batch Updates

updates = [
    (85, 1),
    (91, 2),
    (76, 3),
]


cursor.executemany(
    """
    UPDATE students
    SET score = %s
    WHERE id = %s
    """,
    updates
)


connection.commit()

37. Transactions and Batch Processing

try:

    cursor.executemany(
        """
        INSERT INTO students
        (name, class_id)
        VALUES (%s, %s)
        """,
        records
    )

    connection.commit()

except mysql.connector.Error:

    connection.rollback()

    raise

Transaction boundaries should be chosen according to the desired atomicity, workload size, lock duration, and recovery requirements.

38. Batch Size

A single enormous transaction is not automatically better than smaller batches.

10 Records
   ↓
Commit

10 Records
   ↓
Commit

10 Records
   ↓
Commit

Appropriate batch size depends on transaction requirements, available resources, database configuration, and workload.

39. Database Round Trips

Network communication between Python and the database contributes to application latency.

Python
  ↓
Request
  ↓
Database
  ↓
Response
  ↓
Python

Repeating this cycle thousands of times can be considerably slower than batching suitable operations.

40. The N+1 Query Problem

A common performance problem occurs when an application executes one query to obtain a list and then another query for every item.

Query 1 → Students

Query 2 → Class for Student 1
Query 3 → Class for Student 2
Query 4 → Class for Student 3
...
Query N → Class for Student N

A suitable JOIN can often replace many of these database calls.

SELECT
    s.name,
    c.class_name

FROM students AS s

INNER JOIN classes AS c
    ON s.class_id = c.id;

41. Connection Overhead

Opening a new database connection can involve network and authentication overhead.

# Potentially inefficient pattern

for record in records:

    connection = connect()

    process(record)

    connection.close()

For many workloads, keeping an appropriately managed connection available for a unit of work is more efficient.

42. Connection Pooling

Connection pooling maintains a collection of reusable database connections.

Application
     |
     v
Connection Pool
 /     |      \
DB    DB      DB
Connection Connection Connection

A pool can reduce repeated connection-establishment overhead in applications that perform many database operations.

43. Connection Pooling Example

from mysql.connector import pooling


pool = pooling.MySQLConnectionPool(
    pool_name="school_pool",
    pool_size=5,
    host="localhost",
    user="app_user",
    password="your_password",
    database="school_db"
)


connection = (
    pool.get_connection()
)

try:

    cursor = (
        connection.cursor()
    )

    cursor.execute(
        "SELECT COUNT(*) FROM students"
    )

    print(
        cursor.fetchone()
    )

finally:

    cursor.close()
    connection.close()

Returning the connection to the pool is important so it can be reused by another operation.

44. Measuring Query Time in Python

import time


start = time.perf_counter()


cursor.execute(
    """
    SELECT
        id,
        name

    FROM students
    """
)


rows = cursor.fetchall()


elapsed = (
    time.perf_counter()
    - start
)


print(
    f"Query time: {elapsed:.4f} seconds"
)

Measuring actual execution time is more useful than assuming a query is fast or slow.

45. Measuring pandas Query Time

import time


start = time.perf_counter()


df = pd.read_sql(
    """
    SELECT
        *
    FROM marks
    """,
    connection
)


elapsed = (
    time.perf_counter()
    - start
)


print(
    f"Load time: {elapsed:.4f} seconds"
)

46. Query Performance vs Total Pipeline Time

SQL Execution
      +
Network Transfer
      +
Python Conversion
      +
DataFrame Creation
      +
Data Processing
      =
Total Runtime

A database query that executes quickly can still result in a slow application if it returns excessive data.

47. SQL Aggregation Before Python

If the application needs only an aggregate result, calculating it in SQL can avoid transferring every underlying row.

SELECT
    class_id,
    AVG(marks) AS average_marks

FROM marks

GROUP BY class_id;

Instead of transferring millions of individual marks to Python, the database can return a much smaller summary.

48. Efficient Reporting Pipeline

Large Database
      ↓
WHERE
      ↓
JOIN
      ↓
GROUP BY
      ↓
Aggregate
      ↓
Small Result
      ↓
pandas
      ↓
Visualization

49. Query Result Size

Always consider how much data is being returned.

50. Query Caching Concept

Some applications repeatedly request data that changes infrequently. Caching can reduce repeated database work.

Application
    ↓
Cache?
 /     \
Yes     No
 |       |
Return   Database
         ↓
       Cache
         ↓
       Return

Cache design introduces additional concerns such as expiration, invalidation, consistency, and memory usage.

51. Avoid Premature Optimization

Optimization should be based on evidence rather than assumptions.

Measure
   ↓
Identify Bottleneck
   ↓
Optimize
   ↓
Measure Again
   ↓
Compare

An index added without understanding the workload may provide little benefit while increasing storage and write overhead.

52. Database Performance Checklist

• Check the query execution plan.
• Review indexes.
• Return only required columns.
• Filter rows in SQL.
• Use suitable JOIN conditions.
• Avoid unnecessary database round trips.
• Use batch operations where appropriate.
• Paginate large results.
• Process large datasets in chunks.
• Measure actual execution time.
• Monitor production workloads.

53. Common Optimization Mistakes

54. Practical Example — School Student Search

Suppose an ERP contains 500,000 student records and frequently searches students by admission number.

SELECT
    id,
    admission_no,
    name,
    class_id

FROM students

WHERE admission_no = %s;

If admission numbers are required to be unique, a unique constraint or unique index can support both data integrity and efficient lookup.

CREATE UNIQUE INDEX
idx_students_admission_no

ON students(admission_no);

55. Python Search Function

def find_student(
    connection,
    admission_no
):

    cursor = connection.cursor(
        dictionary=True
    )

    try:

        cursor.execute(
            """
            SELECT
                id,
                admission_no,
                name,
                class_id

            FROM students

            WHERE admission_no = %s
            """,
            (
                admission_no,
            )
        )

        return cursor.fetchone()

    finally:

        cursor.close()

56. Practical Example — Examination Dashboard

Suppose a dashboard needs class-wise average marks rather than every individual mark.

SELECT
    c.class_name,
    AVG(m.marks) AS average_marks

FROM classes AS c

INNER JOIN students AS s
    ON c.id = s.class_id

INNER JOIN marks AS m
    ON s.id = m.student_id

GROUP BY
    c.id,
    c.class_name

ORDER BY
    average_marks DESC;

Returning only the summary required by the dashboard can be much more efficient than loading all raw marks into Python first.

57. Loading Dashboard Data into pandas

query = """
SELECT
    c.class_name,
    AVG(m.marks) AS average_marks

FROM classes AS c

INNER JOIN students AS s
    ON c.id = s.class_id

INNER JOIN marks AS m
    ON s.id = m.student_id

GROUP BY
    c.id,
    c.class_name
"""


df = pd.read_sql(
    query,
    connection
)


print(df)

58. Practical Example — Large CSV to MySQL

A data-science automation pipeline may need to import a very large CSV into MySQL.

CSV File
   ↓
pandas chunks
   ↓
Validate
   ↓
Batch INSERT
   ↓
Commit
   ↓
Next Chunk

59. Chunked Import Example

import pandas as pd


for chunk in pd.read_csv(
    "students.csv",
    chunksize=5000
):

    records = list(
        chunk[
            [
                "name",
                "class_id"
            ]
        ].itertuples(
            index=False,
            name=None
        )
    )

    cursor.executemany(
        """
        INSERT INTO students
        (name, class_id)

        VALUES (%s, %s)
        """,
        records
    )

    connection.commit()

In production, add validation, error handling, logging, and an appropriate transaction strategy around the import process.

60. Profiling the Complete Workflow

Start
  ↓
Connect
  ↓
Execute SQL
  ↓
Fetch
  ↓
Convert
  ↓
Transform
  ↓
Export
  ↓
End

Measure individual stages when investigating performance instead of treating the entire pipeline as one opaque operation.

61. Performance Logging

import logging
import time


logger = logging.getLogger(
    __name__
)


start = time.perf_counter()


cursor.execute(
    """
    SELECT
        id,
        name

    FROM students

    WHERE class_id = %s
    """,
    (
        12,
    )
)


rows = cursor.fetchall()


elapsed = (
    time.perf_counter()
    - start
)


logger.info(
    "Student query returned %d rows in %.4f seconds",
    len(rows),
    elapsed
)

62. Production Monitoring

Performance optimization is an ongoing process. Production monitoring can reveal queries that become slow as data volume changes.

Application
     ↓
Logs / Metrics
     ↓
Slow Queries
     ↓
Analyze
     ↓
Optimize
     ↓
Measure Again

63. Index Design Checklist

• ☐ Identify frequently executed queries.
• ☐ Identify frequent WHERE conditions.
• ☐ Identify common JOIN columns.
• ☐ Consider uniqueness requirements.
• ☐ Evaluate composite indexes carefully.
• ☐ Consider column order.
• ☐ Check existing indexes.
• ☐ Use EXPLAIN.
• ☐ Measure before and after.
• ☐ Consider write overhead.

64. Python Database Optimization Checklist

• ☐ Use parameterized queries.
• ☐ Avoid unnecessary SELECT * queries.
• ☐ Filter data in SQL.
• ☐ Use suitable JOINs.
• ☐ Avoid N+1 queries.
• ☐ Batch suitable inserts and updates.
• ☐ Use pagination for large result sets.
• ☐ Process large datasets in chunks.
• ☐ Reuse connections appropriately.
• ☐ Consider connection pooling for suitable applications.
• ☐ Measure actual performance.
• ☐ Monitor production behavior.

65. Practical Examination Tasks

66. Interview Questions

67. Examination MCQs

68. Coding Challenge — Optimize a School ERP Query

69. Suggested Performance Investigation

Slow Dashboard
      ↓
Capture Query
      ↓
Measure Runtime
      ↓
EXPLAIN
      ↓
Review Indexes
      ↓
Review JOINs
      ↓
Reduce Result Size
      ↓
Optimize
      ↓
Measure Again

70. Self-Assessment Checklist

You should now be able to:

• ☐ Explain database indexes.
• ☐ Create indexes.
• ☐ Create composite indexes.
• ☐ Understand index trade-offs.
• ☐ Inspect indexes.
• ☐ Use EXPLAIN.
• ☐ Measure query execution time.
• ☐ Optimize SELECT statements.
• ☐ Optimize JOIN queries.
• ☐ Avoid unnecessary data transfer.
• ☐ Implement OFFSET pagination.
• ☐ Understand keyset pagination.
• ☐ Process large datasets in chunks.
• ☐ Use executemany() for batch operations.
• ☐ Understand connection pooling.
• ☐ Recognize the N+1 query problem.
• ☐ Measure before and after optimization.
• ☐ Design efficient Python database workflows.

71. Key Takeaway