Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Mysql not caching queries

Posted on 2016-10-13
4
Medium Priority
?
136 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 2000 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:K V
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

609 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