?
Solved

Mysql not caching queries

Posted on 2016-10-13
4
Medium Priority
?
124 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

Independent Software Vendors: 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!

Question has a verified solution.

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

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…
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

764 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