Reduce row retrieval time from big SQLAlchemy database

Question

In my Pyramid application I have models.py:

db = scoped_session(sessionmaker())
Base = declarative_base()

followers = Table('followers',
                  Base.metadata,
                  Column('follower_id', Integer, ForeignKey('artist.id')),
                  Column('followed_id', Integer, ForeignKey('artist.id')))

class Artist(Base):
    __tablename__ = 'artist'
    id = Column(Integer, primary_key=True)
    title = Column(Text)

    followed = relationship('Artist',
                                   secondary=followers,
                                   primaryjoin=(followers.c.follower_id == id),
                                   secondaryjoin=(followers.c.followed_id == id),
                                   backref=backref('followers', lazy='dynamic'),
                                   lazy='dynamic')

Index('my_index', Artist.id, unique=True, mysql_length=255)

My artist table has 85,632 rows and followers auxiliary table has 420,749 rows.

Currently if I try to retrieve followed of some artist like this:

db.query(Artist).first().folowed.all()

The query takes around 30-40 ms to retrieve the rows, how could I improve my model to reduce this time?

By the way my Artist model is based on this tutorial.

Answer 1

Solution:

• Use InnoDB, that way ForeignKeys are handled by the database, not by SQLAlchemy.
• index the field followers.follower_id.