Indexes

Speed up WHERE clauses at the cost of slower writes. Add to columns you filter on.

Code example

class User(Base):
    username = Column(String, unique=True, index=True)
    email    = Column(String, index=True)

# These are now fast:
db.query(User).filter(User.username == 'ada').first()
db.query(User).filter(User.email == 'a@b.com').first()

How it works

An index is a sorted copy of a column that lets the DB find rows without scanning the whole table. Without an index on `username`, a login query has to check every row — O(n). With an index, it's O(log n). The trade-off: every INSERT/UPDATE on an indexed column has to update the index too, so writes get slower. Rule of thumb: index columns you WHERE-filter on (foreign keys, usernames, emails) and don't index columns you only display.