flask-security: minimize the database hits

Question

In my application, I have used flask-security to add authentication and authorization topics. SQLAlchemy is also used as the data provider (with MySQL as backend). The app is working fine.

Then, I did some MySQL tracing, and the log shows me that at every URL requested on the app, the flask-security library send two database queries:

• select ... from user where user.id = 'the user identifier'
• select ... from role, roles_users ...

I think this is a performance problem, and I like to minimize these queries. I don't know if there is a configuration feature I'm missing.

Answer 1

Without going into further optimization (like using Redis to cache SQL responses or user objects) I don't think you can avoid the first request. In most cases you need the data about the user, and you don't want to store these data in the user's session cookie. Again you could use something like Redis to store these info server-side, indexed by the session ID.

However you could probably use a JOIN to avoid the second request. By doing the first and the second request at the same time it would save you some transport time + the DB would be able to select an appropriate query plan.