Query cache not working

[guestisp]

I'm trying to put in cache two huge queries.

With SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC; i'm able to see the digest for these two queries.

So I did:

INSERT INTO mysql_query_rules (active,digest,cache_ttl,apply) VALUES (1,'0x82796C80785917A6',60000,1);
INSERT INTO mysql_query_rules (active,digest,cache_ttl,apply) VALUES (1,'0xC31128202A53C473',60000,1);
LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;

but these queries are still returned from the backend and not from the query cache, as I can see by looking at column hostgroup returned by the SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest_reset ORDER BY sum_time DESC;

Am I miss something ?

[renecannao]

Are you using prepared statements? If yes, prepared statements aren't supported in Query Cache

[guestisp]

I think so , it's a Laravel project and AFAIK it does prepared statement with PDO

[guestisp]

Any plans to add support for prepared statement in query cache ?

[jbrown123]

I suggest making this information (query cache doesn't support prepared statements) prominent in the readme and in the wiki and FAQ. I just spent an entire day trying to figure out why proxySQL wasn't caching my queries (that's the only reason I wanted to use it) only to find a tiny note at the bottom of one page of the wiki under "limitations".

In addition, obviously, I'd like the query cache to support prepared statements.

[peppy]

Is there any chance of this getting attention? Also a laravel user here. There's no easy way to turn off prepared statements even when there are no parameters present in the statement.

As a first step, would it be feasible to cache prepared statements in the case there are no inputs?

[monteozillow]

Just to note, the mysql_query_cache rules are ran in order and stops on the first match, so if you have queries that direct certain types of queries to different hostgroups and you have caching rules, put the directing rules after the caching rules or caching rule won't run if the query matches a directing rule first.

[renecannao]

@monteozillow : if I understand you claim correctly (you meant mysql_query_rules and not mysql_query_cache) , the claim "stops on the first match" is absolutely incorrect. You probably have apply=1.

[monteozillow]

You are correct, I had apply=1 set. I have since put the directing rule for SELECT first with apply=0, and caching rules after. Thanks for the clarification.