1

I want to cache sqlalchemy,i have discovered that using keyword SQL_CACHE in mysql can be helpful in caching queries on demand. But how do i do it in sqlalchemy ? Is it possible ?

3
  • The Query cache is not that useful. If you have slow queries; let's see them and work on speeding them up. Sometimes it is as simple as adding a composite index.
    – Rick James
    Feb 13, 2016 at 5:28
  • Ya you are right about it . But i want to know how do i do it in sqlalchemy ?Is it possible ? Feb 14, 2016 at 13:48
  • <cynicism>Another case of 3rd party software getting in the way.</cynicism>
    – Rick James
    Feb 14, 2016 at 17:42

2 Answers 2

0

You need use a prefix_with. For example, this:

query = session.query(User.name).prefix_with('SQL_CACHE')

would render:

SELECT SQL_CACHE users.name AS users_name FROM users
0

This is a rather old question, but deserves an answer.

You have to set the query_cache_type.

Here are the values and their meaning

  • 0 or OFF : Do not cache results in or retrieve results from the query cache. Note that this does not deallocate the query cache buffer. To do that, you should set query_cache_size to 0.
  • 1 or ON : Cache all cacheable query results except for those that begin with SELECT SQL_NO_CACHE.
  • 2 or DEMAND Cache results only for cacheable queries that begin with SELECT SQL_CACHE.

By default, query_cache_type is 0 (Cache Nothing).

You can run this to cache every SELECT that does not have SQL_NO_CACHE directive in your session:

SET query_cache_type = 1;

You can run this to cache every SELECT that has the SQL_CACHE directive in your session:

SET query_cache_type = 2;

If you want it set globally, then run

SET GLOBAL query_cache_type = 2;

Then add that to the my.cnf

[mysqld]
query_cache_type = 2

PLEASE NOTE THIS TOTALLY GOES AWAY IN MySQL 8.0 !!!

This answer is only good for MySQL 5.7 and prior, also older MariaDB versions

What About SQLAlchemy ???

Since you want the caching on demand, please set query_cache_type to 2.

Then you can run

query = session.query(User.name).prefix_with('SQL_CACHE')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.