Mastering Object-oriented Python

Improving performance with indices

One of the ways to improve the performance of a relational database such as SQLite is to make join operations faster. We don't want SQLite to read an entire table to find matching rows. By building an index on a particular column, SQLite can examine the index and read just the relevant rows from the table.

When we define a column that might be used in a query, we should consider building an index for that column. This is a simple process that uses SQLAlchemy. We simply annotate the attribute of the class with index=True.

We can make fairly minor changes to our Post table, for example. We can do this to add indexes:

class Post(Base): __tablename__ = "POST" id = Column(Integer, primary_key=True) title = Column(String, ...