Solved

Mysql not caching queries

Posted on 2016-10-13
4
110 Views
Last Modified: 2016-10-14
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
Comment
Question by:Rohit Bajaj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 29

Expert Comment

by:fibo
ID: 41843141
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
 

Author Comment

by:Rohit Bajaj
ID: 41843170
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 41843219
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
 
LVL 21

Expert Comment

by:theGhost_k8
ID: 41843978
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question