Solved

How Can I do Query Caching in MySQL?

Posted on 2008-10-23
7
201 Views
Last Modified: 2013-12-13
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
Comment
Question by:phparmy
  • 4
  • 3
7 Comments
 
LVL 26

Expert Comment

by:ushastry
ID: 22794332
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
 

Author Comment

by:phparmy
ID: 22794422
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
 
LVL 26

Expert Comment

by:ushastry
ID: 22794476
.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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:phparmy
ID: 22794520
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
 
LVL 26

Expert Comment

by:ushastry
ID: 22794574
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
 
LVL 26

Accepted Solution

by:
ushastry earned 500 total points
ID: 22794577
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
 

Author Comment

by:phparmy
ID: 22795013
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now