homepage Welcome to WebmasterWorld Guest from 54.226.136.179
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Php MySql Caching Queries
Imaster

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 3348978 posted 6:51 pm on May 24, 2007 (gmt 0)

Hi,

I am writing php scripts for an application. There are several queries like select * from table where name = .... and so on.

I would like such that once any unique query has been executed, it should be cached and should display quickly next time the same query is called unless the mysql table which is being accessed is updated or its timestamp changes.

Is this possible or there are better caching/speed improvements available?

Thanks

 

StupidScript

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 3348978 posted 9:39 pm on May 24, 2007 (gmt 0)

Check out The Query Cache [dev.mysql.com] and also consider Stored Procedures [dev.mysql.com]. (The query cache is the first answer to your question, but I thought you might be interested in stored procedures, too.)

Imaster

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 3348978 posted 8:25 am on May 28, 2007 (gmt 0)

Thanks a ton. Query Cache works perfect and is super fast. I am impressed!

My concern is that I have around millions of different queries and I have set query-cache-size = 64M, but when I issue the command SHOW STATUS LIKE '%qcache%'; [Its almost down to 49 mb [Qcache_free_memory ] after just 11812 queries. What happens when it goes down to 0 mb

mysql> SHOW STATUS LIKE '%qcache%';
+-------------------------+----------+
¦ Variable_name ¦ Value ¦
+-------------------------+----------+
¦ Qcache_queries_in_cache ¦ 11812 ¦
¦ Qcache_inserts ¦ 11812 ¦
¦ Qcache_hits ¦ 6783 ¦
¦ Qcache_lowmem_prunes ¦ 0 ¦
¦ Qcache_not_cached ¦ 18 ¦
¦ Qcache_free_memory ¦ 49025296 ¦
¦ Qcache_free_blocks ¦ 1 ¦
¦ Qcache_total_blocks ¦ 27048 ¦
+-------------------------+----------+

Or since I have so many queries, should I increase query-cache-size to more? I have 2GB Ram.

Imaster

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 3348978 posted 10:46 am on May 28, 2007 (gmt 0)

Funny, after an hour the free memory has increased again even though cached queries have increased.

mysql> SHOW STATUS LIKE '%qcache%';
+-------------------------+---------+
¦ Variable_name ¦ Value ¦
+-------------------------+---------+
¦ Qcache_queries_in_cache ¦ 38207 ¦
¦ Qcache_inserts ¦ 70526 ¦
¦ Qcache_hits ¦ 67446 ¦
¦ Qcache_lowmem_prunes ¦ 32319 ¦
¦ Qcache_not_cached ¦ 22 ¦
¦ Qcache_free_memory ¦ 6890888 ¦
¦ Qcache_free_blocks ¦ 1796 ¦
¦ Qcache_total_blocks ¦ 86610 ¦
+-------------------------+---------+
8 rows in set (0.00 sec)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved