Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

How Can I do Query Caching in MySQL?

I have some select queries that i use frequently. But these queries are important any change in database cache has to rebuild. Anyone have idea.
<?php
$query="select username from users where status='1'";
if(/* is there any changes made after last cache i saved */){
  $result=mysql_query($query);
}else{
  $result=/*last_cache*/;
}
 
//what can i replace /**/ wrote i. i dont know how to be sure is there any changes in tables and i dont know to cache query.
//Help Me:(
 
?>

Open in new window

0
phparmy
Asked:
phparmy
  • 4
  • 3
1 Solution
 
UmeshMySQL Principle Technical Support EngineerCommented:
Two query cache-related options may be specified in SELECT statements:

SQL_CACHE

The query result is cached if it is cacheable and the value of the query_cache_type system variable is ON or DEMAND.

SQL_NO_CACHE

The query result is not cached.

Examples:

select SQL_CACHE username from users where status='1';

select SQL_NO_CACHE username from users where status='1';


Take a look at the manual pages for more details (pls note that you need to change MySQL system parameters in order to work)

http://dev.mysql.com/doc/refman/5.0/en/query-cache.html
0
 
phparmyAuthor Commented:
iam dont going to cache my all queries. i want to cache some specific queries. could anyone show me little basic code that caches query.
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
.To catch specific queries you have to pass the hint "SQL_CACHE" before your query.. in order to work properly you MySQL server has to be enabled..

SHOW VARIABLES LIKE 'have_query_cache'; =>>>>> should be " Yes"
SHOW VARIABLES LIKE 'query_cache_type'; ===> should be "2" which is on demand

Let me know if you are not clear about my comments...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
phparmyAuthor Commented:
i understant your commnets. i read about it in mysql site. but how can i user below code i write. could you say how can i write it properly.
<?php
 
$query="select username from users where status='1'";
if(/* is there any changes made after last cache i saved */){
  $result=mysql_query(cache this query = $query);
}else{
  $result=/*last_cache*/;
}
 
while($list=mysql_fetch_assoc($result)){
  echo $list['username'];
} 
 
?>

Open in new window

0
 
UmeshMySQL Principle Technical Support EngineerCommented:
I assume that you have checked MySQL server parameters which I mentioned earlier(query cache type is on demand i.e 2 & have_query_cache=yes)

To monitor query cache performance, use SHOW STATUS to view the cache status variables:

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_free_blocks      | 36     |
| Qcache_free_memory      | 138488 |
| Qcache_hits             | 79570  |
| Qcache_inserts          | 27087  |
| Qcache_lowmem_prunes    | 3114   |
| Qcache_not_cached       | 22989  |
| Qcache_queries_in_cache | 415    |
| Qcache_total_blocks     | 912    |
+-------------------------+--------+

SQL-CACHE.TXT
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
You code should look like this..

<?php
 
# Pass the SQL_CACHE hint to MySQL server

$query="select SQL_CACHE username from users where status='1'";
$result=mysql_query($query);
 
while($list=mysql_fetch_assoc($result)){
  echo $list['username'];
}
 
?>
0
 
phparmyAuthor Commented:
Thanks i understand. Do you know how set interval that mysql caches query after 2 hours previous one it cahces. Like below.
<?php
 
$query="select username from users where status='1'";
if(/* if two hours passed, query previous one i cached cache again */){
  $result=mysql_query(cache this query = $query);
}else{
  $result=/*last_cache*/;
}
 
while($list=mysql_fetch_assoc($result)){
  echo $list['username'];
} 
 
?>

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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