Defining indices in the ORM layer

One of the ways to improve the performance of a relational database such as SQLite is to make join operations faster. The ideal way to do this is to include enough index information so that slow search operations aren't done to find matching rows.

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, index=True) 
    date = Column(DateTime, index=True) 
    blog_id = Column(Integer, ForeignKey('BLOG.id'), index=True) 

Adding two indexes for the title...