• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 140
  • Last Modified:

Mysql not caching queries

HI,
Following are some screen shots of mysql server running on my computer locally....
Following are the queries i ran  :
SHOW VARIABLES LIKE '%query_cache%';
SHOW STATUS LIKE '%qcache%';

select * from notes where id = '0002c1ff-d8a1-4cee-a499-448332c914de';

Open in new window

I have attached the result of above queries before and after the select * query execution....
The only change i see in qcache is  : Qcache_not_cached was 1 before select execution and after that it is 2.
Which signifies that the select query was not cached...
What could be the reason that this query was not cached...

How can i make the caching available ?

Thanks
qcache-after-select-execution.png
select-query.png
qcache-before-select-execution.png
query-cache-before-select-execution.png
0
Rohit Bajaj
Asked:
Rohit Bajaj
1 Solution
 
fiboCommented:
What happens if instead of count(*) you use the primary index (eg, prim_id) with count(prim_id)?
And instead of SELECT * use the field names?

Finally, your "query" is really "2 queries" and this might impact the value you see after the second query. What is the cache after the 1st query?
0
 
Rohit BajajAuthor Commented:
HI,
The select count(*) from notes query i didnt execute... by mistake i left it there..
Only query i executed is select * from notes where id = ...
so its just one query...
You can ignore the count(*) query
1
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
query_cache_type is off, so no caching is done.
http://dev.mysql.com/doc/refman/5.7/en/query-cache-configuration.html

so, you put this:
SET SESSION query_cache_type = ON;

to change that
1
 
K VDatabase ConsultantCommented:
If you're using MySQL 5.7 and all InnoDB with proper indexing and bufferpool why would you worry about query cache!!! This has been very very old tutorial you must refer to understand the optimal size of query_cache for your systems:---> https://dom.as/tech/query-cache-tuner/ <-- Follow that for sure.

Also just saying that SET SESSION changes will vanish with the terminated connection. SET GLOBAL changes will vanish with mysqld restart. All persistent changes should be kept in config.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now